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.
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;