💾 Archived View for radia.bortzmeyer.org › fosdem › event-11481.gmi captured on 2024-12-17 at 10:02:19. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2023-06-14)
-=-=-=-=-=-=-
Pavel Borisov
Type devroom
Starts on day 1 (2021-02-06) at 15:30 (Brussels time, UTC+1) in room Postgresql (duration 00:30)
Matrix room #postgresql:fosdem.org
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.