<div dir="ltr"><div dir="ltr">Hi Han,<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Il giorno lun 26 lug 2021 alle ore 14:21 Han Wang <<a href="mailto:hanwgeek@gmail.com">hanwgeek@gmail.com</a>> ha scritto:<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 dir="ltr"><div dir="ltr"><div dir="ltr"><div>Hi Giuseppe and Hi Regina,</div><div><br></div><div>After checking the paper of GiST and implementation in Postgres. I think the query performance should be considered besides the building process. In the larger data test scenario, the building time of different indexes are similar because Postgres just hashes the tuples and sorts them and packs them into pages, building the tree index from bottom to up. With a bad hash order definition, the building process cannot detect the poor index query performance. So it is necessary to test the index query performance. I have tested the query performance with the `EXPLAIN` operator, using the sql scripts like other indexes in the `/regress`. But I am not familiar with PL/pgSQL, so I handle the log with some python scripts.</div><div><br></div><div>In this test, I focus on the buffer hits and execution time of different tasks of different index types including `No Index`, `Simple GiST index`, `X hash function`, `morton hash function` and `hilbert hash function`. And there are some results:</div><div>Shared buffer hits:</div><div><table style="box-sizing:unset;border-collapse:collapse;border-spacing:0px;border:medium none;color:rgba(0,0,0,0.87);font-size:12px;table-layout:fixed;margin-left:0px;margin-right:0px;margin-bottom:1em;font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol"" border="1"><thead style="box-sizing:unset;vertical-align:bottom"><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"> Index</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">Create Time(ms)</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&&</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">~=</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">~</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">@</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">|>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">|&></th></tr></thead><tbody style="box-sizing:unset"><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">0</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">No Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">0</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">18</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">1</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">GiST Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">25.249</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">40237</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">46085</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">40297</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">46025</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">41994</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">45295</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">41934</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">45355</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">2</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">X PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">8.829</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">443620</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">441235</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">444501</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">440354</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">441568</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">442503</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">440687</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">443384</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">3</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">Morton PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">16.885</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">447779</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">447446</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">448669</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">446556</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">445362</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">449428</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">444472</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">450318</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">4</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">Hilbert PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">16.824</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">446714</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">444058</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">447600</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">443172</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">446072</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">445394</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">445186</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">446280</td></tr></tbody></table></div><div>Execution time:</div><div><br></div><div><table style="box-sizing:unset;border-collapse:collapse;border-spacing:0px;border:medium none;color:rgba(0,0,0,0.87);font-size:12px;table-layout:fixed;margin-left:0px;margin-right:0px;margin-bottom:1em;font-family:-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif,"Apple Color Emoji","Segoe UI Emoji","Segoe UI Symbol"" border="1"><thead style="box-sizing:unset;vertical-align:bottom"><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">Index</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">Create Time(ms)</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&&</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">~=</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">~</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">@</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">&<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">|>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">|&></th></tr></thead><tbody style="box-sizing:unset"><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">0</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">No Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">0</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">567.251</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">565.720</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">481.618</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">561.877</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">563.452</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">480.128</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">478.275</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">478.518</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">567.144</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">572.191</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">563.255</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">556.281</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">1</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">GiST Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">25.249</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">289.255</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">293.143</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">28.838</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">289.002</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">291.296</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">28.336</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">28.597</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">26.947</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">295.394</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">297.556</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">293.988</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">299.760</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">2</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">X PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">8.829</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">440.861</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">445.630</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">37.960</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">439.564</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">440.535</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">37.594</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">37.979</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">37.741</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">386.662</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">384.635</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">385.166</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">388.832</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">3</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">Morton PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">16.885</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">421.999</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">413.427</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">77.002</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">422.939</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">412.130</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">77.415</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">75.102</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">76.056</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">416.205</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">446.599</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">410.614</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">434.613</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none;text-align:right">4</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">Hilbert PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">16.824</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">417.539</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">415.962</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">56.583</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">421.226</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">414.553</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">56.320</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">55.600</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">55.338</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">416.639</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">421.243</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">418.550</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:medium none">417.094</td></tr></tbody></table></div><div>The number of shared buffer hits are far bigger than the original one. But what confuses me is that the execution times are worse. I am trying to figure out why this happened. </div><div>What's more, I am not very clear about the relationship between query performance and the number of shared buffer hits.</div><div><br></div><div>If you have any questions or suggestions, please let me know.</div></div></div></div></blockquote><div><br></div><div>I see you are considering there a self-join query for your tests: I'd follow the good suggestions you already received considering a simpler query and just with</div><div>one operator &&, i.e. something like</div><div><br></div><div><span style="font-family:monospace">                SELECT * FROM table WHERE geom_column && BOX2D('...')</span></div><div><br></div><div>and check the amount of buffers hit/read in each plan obtained in the three distinct contexts (no index ÷ GiST index no presort ÷ GiST index with presort).</div><div><br></div><div>Also, I'd check in detail what's stored in the index obtained with or without the presorting. In PostgreSQL 14dev they added the support for running the</div><div><a href="https://www.postgresql.org/docs/14/pageinspect.html#id-1.11.7.32.9">pageinspect</a> functions even on GiST indexes. In this way you can check which information is actually stored in each page of the built indexes, and check</div><div>for any big difference in the nodes' structures.</div><div><br></div><div>Giuseppe.<br></div></div></div>