[postgis-devel] GiST Sorting

[Paul Ramsey]

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
> >>
>