[postgis-devel] GiST Sorting

Mon Jan 17 17:16:45 PST 2022

Darafei,

I retested this using the patch attached to https://commitfest.postgresql.org/36/3487/ (https://www.postgresql.org/message-id/attachment/129685/02_reduce_page_overlap_of_gist_indexes_built_using_sorted_method.patch)

Results summarized here (top is my desktop and bottom are tests on Reallie)

https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/run_results/postgis_index_performance_2022_01.md

Unfortunately I hadn’t saved my old results but I can rerun again without the patch to get a truer sense of how the patch improves or makes things worse.

So general conclusions

1) the index build as you noted we lost some speed so it’s now at best only twice as fast compared to  3 times faster of before

2) Most of the intersect / box checks are faster with the gist sort, but the one that Paul was whining about is better but still a bit slower with the gist sort patch enabled) – except for the roads 2d case (note Paul’s roads had a Z component and the roads 2d I did a ST_Force2D on them to get the 2D versions)

That said I’m not sure we much care about the 3D case.

I think Reallie is much slower because I have crappy disks for her and my desktop disk is SSD.

Thanks,

Regina

From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Darafei "Kom?pa" Praliaskouski
Sent: Sunday, January 9, 2022 9:12 AM
To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: [postgis-devel] GiST Sorting

Hi everyone,

After several weeks of research, testing and experiments we believe we found a solution that does not cause the select performance degradation (or sometimes brings profit) and still builds the index faster using the sorting build method. 

The patch is posted on pgsql-hackers by Aliaskandr Kalenik in this thread:

https://www.postgresql.org/message-id/CAHqSB9jqtS94e9%3D0vxqQX5dxQA89N95UKyz-%3DA7Y%2B_YJt%2BVW5A%40mail.gmail.com

Patch on commitfest:

https://commitfest.postgresql.org/36/3487/

Sergei Shoulbakov implemented the benchmark to prove the effect. Results and notes are posted here:
https://www.postgresql.org/message-id/3aa6ba30-e9d8-10ef-753f-8deea5f196d0%40kontur.io

We need your help to get it reviewed and merged to get the benefits for everyone in PG15. 

Darafei. 

On Fri, Dec 3, 2021 at 10:19 PM Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> > wrote:

This is done at
https://github.com/postgis/postgis/commit/d8976c0d2f6b8906b583f24e98fba2ef92fb1277

On Fri, Dec 3, 2021 at 10:37 AM Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> > wrote:
>
> Nevermind, I found the secret incantation:
>
> alter operator family gist_geometry_ops_2d using gist drop function 11 (geometry);
>
> alter operator family gist_geometry_ops_2d using gist add function 11 (geometry) geometry_gist_sortsupport_2d (internal);
>
> P.
>
> > On Dec 2, 2021, at 12:57 PM, Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> > wrote:
> >
> > Ding ding!
> > I can strip the sort support function out of the default opclass, but
> > we don't have any way to add it back in, except with a direct
> > alteration of the system tables. If we're OK with that, I will figure
> > out the magic SQL incantation so we can document it, and strip the
> > sort support function from the opclass.
> >
> > P
> >
> > On Wed, Dec 1, 2021 at 1:10 PM Paul Ramsey <pramsey at cleverelephant.ca <mailto:pramsey at cleverelephant.ca> > wrote:
> >>
> >> So about this plan, I don't see the ALTER OPERATOR CLASS recipe that can do the thing we want.
> >>
> >> https://www.postgresql.org/docs/14/sql-alteropclass.html
> >>
> >> What's the magic incantation?
> >>
> >>> On Dec 1, 2021, at 8:30 AM, Regina Obe <lr at pcorp.us <mailto:lr at pcorp.us> > wrote:
> >>>
> >>>> 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.
> >>> [Regina Obe]
> >>> I don't think I was in this discussion about CREATE INDEX ON table USING gist(column) WITH
> >>>> (sortfunction=zcurve).
> >>>
> >>> What was the unnecessary complication?  Was it deemed "user-facing" too complicated
> >>> or "under the hood" too complicated.
> >>>
> >>> For user-facing I don't see it as too complicated.  Granted it is a bit extra.
> >>> But if it is deemed always not the absolute best, I feel a configure option is fine.
> >>> It's only giving users redundant equally good options that I would consider too complicated.
> >>>
> >>>
> >>> _______________________________________________
> >>> postgis-devel mailing list
> >>> postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
> >>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >>
>
_______________________________________________
postgis-devel mailing list
postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-devel

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20220117/7f6d2c90/attachment.html>