2

How can I check the page size (and maybe modify) of the GiST index in PostGIS? From the PostGIS documentation, I quote:

"... GiST indexes support the concept of "lossiness" which is important when dealing with GIS objects larger than the PostgreSQL 8K page size. Lossiness allows PostgreSQL to store only the "important" part of an object in an index -- in the case of GIS objects, just the bounding box. GIS objects larger than 8K will cause R-Tree indexes to fail in the process of being built."

Not only am I having some trouble understanding this whole concept but I am a bit confused what this implies for my original question. What does this mean that the page size is? Is it necessarily 8K?

1

I am not an expert. From what I've seen:

  1. Regarding the page size, 8K is the default, and you can change it when compiling Postgres

  2. Regarding the "lossiness", an index entry is a pointer to the actual tuple/record/data row on the disk. Being a pointer, the index entry should usually be small enough to fit in one page (8K by default). A second component of an index entry is the value (numeric or spatial) based on which the index is defined. When it comes to a spatial/geometric object, that value is the geometry itself (or some spatial "summary" of it). This is where the lossiness comes in, if used directly in the GiST index, the geometry object can theoretically exceed the page size (if you have a long polyline, e.g.). That's why in general, using a summary in the index entry is safer space-wise. With a bounding box as a summary of the geometry, you need ALWAYS only two points.

Hence their statement,

Lossiness allows PostgreSQL to store only the "important" part of an object in an index

I hope this helps clarifying the concepts a bit.

1

The answer from tinlyx already said that with PostgreSQL the page size is always 8k when you don´t change it (which you should´nt without knowing exactly what you are doing).

But i will try to give some background explanation about it for better understanding. But i do not know how the implementation in PostgreSQL and PostGIS is in the very detail as i´m no programmer.

A page in general is the way data is safed and used in memory allocation and even in the registers of the processor. Each page has some header with some general information and then the data. in PostgreSQL each row is a page. When a row has more information as 8k the row is split into multiple pages, wich only affects performance and complicates the background stuff for the software. But an index can´t have a branch-object in more than one page. The documentation might be a bit unfortunatly worded as it tells from a general problem that does not occur with spatial data in an R-Tree as all objects are only used as their bounding boxes.

The background is complicated, especially how the software balances the tree and how that affects the size of the index and performance.

But a few keywords that might help you with the general idea:

  • Indeces are made to find objects, without it all data would need to be scanned sequential to find a single value; for spatial data it means for example that to check for overlapping polygons all polygons would have to be checked against each other
  • a tree index means that it starts from a root towards branches and subbranches
  • the index used here is a R-Tree, which sorts spatial data based on their bounding boxes (r - for rectangle); this way the check for overlapping is done only between few rectangles and in case of overlapping it heads further on in the tree until it finds a single object (the leaf); all objects in the non-overlapping rectangles don´t need to be scanned anymore

  • each branch might have different amount of objects in it, the index (or the software implementation) tries to fill as many objects into it as the page size allows by growing the bounding rectangle of the branch around the objects without touching the other branches

  • the index also tries to sort the data to have as few subbranches as possible and to have as few overlappings in the boundingboxes for the branches for each index-level; both is important for the performance of the index

  • this sorting is done by the software that implements the GiST; PostgreSQL is one implemention and offers an API to access this "sorting-program"; PostGIS don´t uses the PostgreSQL R-Tree but building its own using the given API from PostgreSQL

  • losiness only means that objects might be compressed using lossy compression (like with JPEG) for the use in the index; as spatial data is only used as bounding-boxes that does not mean anything in this context here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.