Online / 6 & 7 February 2021

schedule

Performance improvements and new use scenarios for SPGiST access method


In the PostgreSQL core, we have the SPGiST index, which is more lightweight and significantly faster than GiST for the supported data types. In many cases, it is the most suitable option for geometric data like points, boxes, etc. An easy way to increase the selection speed is to eliminate the need for the results recheck by a table. But with the index being single-column by design we could not enjoy index-only scans for multicolumn queries.

The talk covers my improvement of the SPGiST access method. Non-key columns can now be included in a single key-column index in a way, similar to that have in GiST and B-tree. This adds the ability to make fast index-only scans for multicolumn queries. Also, non-key columns with data types without SPGiST opclass can be added, which creates an opportunity for the new use cases. In my presentation, I will justify the modification, discuss new use cases of GiST and SPGiST indexes, and share some performance benchmarks.

Speakers

Photo of Pavel Borisov Pavel Borisov

Links