Warm tip: This article is reproduced from stackoverflow.com, please click
postgresql

How does Postgres combine spatial and non-spatial data into one index

发布于 2020-03-29 21:00:01

As far as I understand we can create a multi-column index on both spatial data (e.g. geospatial) and non-spatial data (e.g. time).

I have also read that Postgres uses B+-trees as the default data-structure for timestamps, ints etc and R-trees for spatial data.

However I do not understand how these two data-types (e.g. geospatial and timestamp) can be combined into one index (in terms of how to underlying data-structure would look like).

For example, if we have created an index on two integer columns we will use a multi-column B+-tree data-structure (as described here https://www.qwertee.io/blog/postgresql-b-tree-index-explained-part-1/).

How would this work with spatial and none-spatial? Will we embed a R-tree at the leaf of the B-tree or vice versa? Or something else?

Any clarification is appreciated.

Questioner
user7641438
Viewed
23
Laurenz Albe 2020-01-31 18:21

A GiST index is a generalization of a B-tree index.

Roughly speaking, the difference is that each down-pointer in a non-leaf page is not associated with an interval between two values, but with a generalized search condition. In the case of geo-spatial data, this generalized search condition is a bounding box: if the item you search for overlaps with the bounding box, you have to descend that branch of the index tree.

Since a GiST index is a generalized B-tree index, it is easy to implement a B-tree index as a special case of a GiST index. This is normally not done, because the regular B-tree index is highly optimized and will perform better, but it can be useful for a multi-column GiST index.

To use a GiST index with a “regular” totally ordered data type, you need to install the additional required operator classes with

CREATE EXTENSION btree_gist;