[postgis-devel] GiST Sorting

[Andrey Borodin]

Hi everyone!

> 30 нояб. 2021 г., в 04:08, Paul Ramsey <pramsey at cleverelephant.ca> написал(а):
> 
> we have multiple tests now that show that the index is slower for query performance

Yes, surely new build is not magical :) It's fast because it builds a not that good index. Not like absolutely bad, but far from optimal.
BTW current buffering build is not building optimal index too, but slightly better than sorting build.
Initially there was a patch in patchset making user specify sorting function during CREATE INDEX ON table USING gist(column) WITH (sortfunction=zcurve). But everyone said that it's unnessecary complication and we just made the function part of the opclass.
But sorting build was not meant to be default!

I'm going to work on this in December, so let's figure out what exactly do we want from PostgreSQL 15?

I'm planning to work on:
1. Fix the concurrency problem in IndexOnlyScan that Peter G. pointed out [0] This will further slow down GiST queries a little, sorry.
2. Sorting items within leaf page in tid order, not provided sorting function.
3. Make better splits of leaf pages. Currently we choose how to split leaf pages solely by sorting function. I want to use split function too. This will consume slightly more memory, but will build better index.

Do we need some functionality to easily enable\disable sorting build? I think chances are very high that we can make it better. But it still will be neither perfect, nor better than buffered build.

Thanks!

Best regards, Andrey Borodin.

[0] https://www.postgresql.org/message-id/CAH2-Wz=PqOziyRSrnN5jAtfXWXY7-BJcHz9S355LH8Dt=5qxWQ@mail.gmail.com