<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Darafei,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I retested this using the patch attached to <a href="https://commitfest.postgresql.org/36/3487/">https://commitfest.postgresql.org/36/3487/</a> (https://www.postgresql.org/message-id/attachment/129685/02_reduce_page_overlap_of_gist_indexes_built_using_sorted_method.patch)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Results summarized here (top is my desktop and bottom are tests on Reallie)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><a href="https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/run_results/postgis_index_performance_2022_01.md">https://git.osgeo.org/gitea/postgis/postgis-performance/src/branch/master/run_results/postgis_index_performance_2022_01.md</a><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So general conclusions<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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)<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That said I’m not sure we much care about the 3D case.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think Reallie is much slower because I have crappy disks for her and my desktop disk is SSD.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-devel [mailto:postgis-devel-bounces@lists.osgeo.org] <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 <postgis-devel@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-devel] GiST Sorting<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Hi everyone,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></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. <o:p></o:p></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">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/">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">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. <o:p></o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><br>Darafei. <o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Fri, Dec 3, 2021 at 10:19 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;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><o:p></o:p></p></blockquote></div></div></div></body></html>