[postgis-devel] GiST Sorting

Paul Ramsey pramsey at cleverelephant.ca
Tue Feb 8 14:36:30 PST 2022 

Congratulations!
I would like to run a couple tests first, but it sounds like enabling for Pg15+ will make sense.
Great job getting this so quickly into Pg.
P

> On Feb 8, 2022, at 1:00 PM, Darafei Komяpa Praliaskouski <me at komzpa.net> wrote:
> 
> Hi,
> 
> thanks everyone for the efforts, sorted build method improvement got merged into Postgres 15.
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ea98a7975e15cefdb446385880a2f55224ee7d
> 
> It's ok to flip it to be default index build method now.
> 
> On Tue, Jan 18, 2022 at 4:16 AM Regina Obe <lr at pcorp.us> wrote:
> 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> 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> 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> 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> 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> 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
> > >>> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> > >>
> >
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel

More information about the postgis-devel mailing list