<div dir="ltr">Hi,<div><br></div><div>thanks everyone for the efforts, sorted build method improvement got merged into Postgres 15.<br><a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ea98a7975e15cefdb446385880a2f55224ee7d">https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1ea98a7975e15cefdb446385880a2f55224ee7d</a><br><br>It's ok to flip it to be default index build method now.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Jan 18, 2022 at 4:16 AM Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div lang="EN-US"><div class="gmail-m_3163530518083483266WordSection1"><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Darafei,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I retested this using the patch attached to <a href="https://commitfest.postgresql.org/36/3487/" target="_blank">https://commitfest.postgresql.org/36/3487/</a> (<a href="https://www.postgresql.org/message-id/attachment/129685/02_reduce_page_overlap_of_gist_indexes_built_using_sorted_method.patch" target="_blank">https://www.postgresql.org/message-id/attachment/129685/02_reduce_page_overlap_of_gist_indexes_built_using_sorted_method.patch</a>)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Results summarized here (top is my desktop and bottom are tests on Reallie)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><a href="https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/run_results/postgis_index_performance_2022_01.md" target="_blank">https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/run_results/postgis_index_performance_2022_01.md</a><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">So general conclusions<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">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)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">That said I’m not sure we much care about the 3D case.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">I think Reallie is much slower because I have crappy disks for her and my desktop disk is SSD.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Thanks,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Regina<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><div style="border-top:none;border-right:none;border-bottom:none;border-left:1.5pt solid blue;padding:0in 0in 0in 4pt"><div><div style="border-right:none;border-bottom:none;border-left:none;border-top:1pt solid rgb(225,225,225);padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span style="font-size:11pt;font-family:Calibri,sans-serif"> postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Darafei "Kom?pa" Praliaskouski<br><b>Sent:</b> Sunday, January 9, 2022 9:12 AM<br><b>To:</b> PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br><b>Subject:</b> Re: [postgis-devel] GiST Sorting<u></u><u></u></span></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">Hi everyone,<u></u><u></u></p><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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. <u></u><u></u></p></div><div><p class="MsoNormal"><br>The patch is posted on pgsql-hackers by Aliaskandr Kalenik in this thread:<br><br><a href="https://www.postgresql.org/message-id/CAHqSB9jqtS94e9%3D0vxqQX5dxQA89N95UKyz-%3DA7Y%2B_YJt%2BVW5A%40mail.gmail.com" target="_blank">https://www.postgresql.org/message-id/CAHqSB9jqtS94e9%3D0vxqQX5dxQA89N95UKyz-%3DA7Y%2B_YJt%2BVW5A%40mail.gmail.com</a><br><br>Patch on commitfest:<br><br><a href="https://commitfest.postgresql.org/36/3487/" target="_blank">https://commitfest.postgresql.org/36/3487/</a><br><br>Sergei Shoulbakov implemented the benchmark to prove the effect. Results and notes are posted here:<br><a href="https://www.postgresql.org/message-id/3aa6ba30-e9d8-10ef-753f-8deea5f196d0%40kontur.io" target="_blank">https://www.postgresql.org/message-id/3aa6ba30-e9d8-10ef-753f-8deea5f196d0%40kontur.io</a><br><br>We need your help to get it reviewed and merged to get the benefits for everyone in PG15. <u></u><u></u></p></div><div><p class="MsoNormal" style="margin-bottom:12pt"><br>Darafei. <u></u><u></u></p></div></div><p class="MsoNormal"><u></u> <u></u></p><div><div><p class="MsoNormal">On Fri, Dec 3, 2021 at 10:19 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<u></u><u></u></p></div><blockquote style="border-top:none;border-right:none;border-bottom:none;border-left:1pt solid rgb(204,204,204);padding:0in 0in 0in 6pt;margin-left:4.8pt;margin-right:0in"><p class="MsoNormal">This is done at<br><a href="https://github.com/postgis/postgis/commit/d8976c0d2f6b8906b583f24e98fba2ef92fb1277" target="_blank">https://github.com/postgis/postgis/commit/d8976c0d2f6b8906b583f24e98fba2ef92fb1277</a><br><br>On Fri, Dec 3, 2021 at 10:37 AM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br>><br>> Nevermind, I found the secret incantation:<br>><br>> alter operator family gist_geometry_ops_2d using gist drop function 11 (geometry);<br>><br>> alter operator family gist_geometry_ops_2d using gist add function 11 (geometry) geometry_gist_sortsupport_2d (internal);<br>><br>> P.<br>><br>> > On Dec 2, 2021, at 12:57 PM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br>> ><br>> > Ding ding!<br>> > I can strip the sort support function out of the default opclass, but<br>> > we don't have any way to add it back in, except with a direct<br>> > alteration of the system tables. If we're OK with that, I will figure<br>> > out the magic SQL incantation so we can document it, and strip the<br>> > sort support function from the opclass.<br>> ><br>> > P<br>> ><br>> > On Wed, Dec 1, 2021 at 1:10 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>> wrote:<br>> >><br>> >> So about this plan, I don't see the ALTER OPERATOR CLASS recipe that can do the thing we want.<br>> >><br>> >> <a href="https://www.postgresql.org/docs/14/sql-alteropclass.html" target="_blank">https://www.postgresql.org/docs/14/sql-alteropclass.html</a><br>> >><br>> >> What's the magic incantation?<br>> >><br>> >>> On Dec 1, 2021, at 8:30 AM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>> >>><br>> >>>> Initially there was a patch in patchset making user specify sorting function<br>> >>>> during CREATE INDEX ON table USING gist(column) WITH<br>> >>>> (sortfunction=zcurve). But everyone said that it's unnessecary complication and<br>> >>>> we just made the function part of the opclass.<br>> >>>> But sorting build was not meant to be default!<br>> >>>><br>> >>>> I'm going to work on this in December, so let's figure out what exactly do we<br>> >>>> want from PostgreSQL 15?<br>> >>>><br>> >>>> I'm planning to work on:<br>> >>>> 1. Fix the concurrency problem in IndexOnlyScan that Peter G. pointed out [0]<br>> >>>> This will further slow down GiST queries a little, sorry.<br>> >>>> 2. Sorting items within leaf page in tid order, not provided sorting function.<br>> >>>> 3. Make better splits of leaf pages. Currently we choose how to split leaf pages<br>> >>>> solely by sorting function. I want to use split function too. This will consume<br>> >>>> slightly more memory, but will build better index.<br>> >>>><br>> >>>> Do we need some functionality to easily enable\disable sorting build? I think<br>> >>>> chances are very high that we can make it better. But it still will be neither<br>> >>>> perfect, nor better than buffered build.<br>> >>>><br>> >>>> Thanks!<br>> >>>><br>> >>>> Best regards, Andrey Borodin.<br>> >>> [Regina Obe]<br>> >>> I don't think I was in this discussion about CREATE INDEX ON table USING gist(column) WITH<br>> >>>> (sortfunction=zcurve).<br>> >>><br>> >>> What was the unnecessary complication?  Was it deemed "user-facing" too complicated<br>> >>> or "under the hood" too complicated.<br>> >>><br>> >>> For user-facing I don't see it as too complicated.  Granted it is a bit extra.<br>> >>> But if it is deemed always not the absolute best, I feel a configure option is fine.<br>> >>> It's only giving users redundant equally good options that I would consider too complicated.<br>> >>><br>> >>><br>> >>> _______________________________________________<br>> >>> postgis-devel mailing list<br>> >>> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>> >>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>> >><br>><br>_______________________________________________<br>postgis-devel mailing list<br><a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><u></u><u></u></p></blockquote></div></div></div></div>_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
</blockquote></div>