<div dir="ltr"><div>Hi Han,</div><div><br></div>> After checking the paper of GiST and implementation in Postgres. <div>> ...  And there are some results:<br>>  If you have any questions <br></div><div><br>Is this the latest version of Postgres?   ( master | PG14beta2 | PG13.3 )</div><div><br></div><div>comment:  The PostgreSQL14 - has some GIST improvements:  <a href="https://www.postgresql.org/docs/release/14.0/">https://www.postgresql.org/docs/release/14.0/</a></div><div><br></div><div>Regards,</div><div>   Imre</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Han Wang <<a href="mailto:hanwgeek@gmail.com">hanwgeek@gmail.com</a>> ezt írta (időpont: 2021. júl. 26., H, 15:21):<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 border="1" style="box-sizing:unset;border-collapse:collapse;border-spacing:0px;border: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""><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:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"> Index</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border: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:none;text-align:right"><<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&&</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">~=</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">~</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">@</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">|>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border: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:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">0</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">No Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">0</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">18</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">1</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">GiST Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">25.249</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">40237</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">46085</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">40297</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">46025</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">41994</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">45295</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">41934</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">45355</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">2</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">X PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">8.829</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">443620</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">441235</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">444501</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">440354</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3009</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">441568</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">442503</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">440687</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">443384</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">3</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">Morton PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">16.885</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">447779</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">447446</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">448669</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">446556</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">4079</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">445362</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">449428</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">444472</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">450318</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">4</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">Hilbert PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">16.824</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">446714</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">444058</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">447600</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">443172</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">3558</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">446072</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">445394</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">445186</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">446280</td></tr></tbody></table></div><div>Execution time:</div><div><br></div><div><table border="1" style="box-sizing:unset;border-collapse:collapse;border-spacing:0px;border: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""><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:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">Index</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border: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:none;text-align:right"><<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&<</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&&</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">~=</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">~</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">@</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">&<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><<|</th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">|>></th><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border: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:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">0</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">No Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">0</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">567.251</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">565.720</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">481.618</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">561.877</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">563.452</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">480.128</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">478.275</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">478.518</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">567.144</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">572.191</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">563.255</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">556.281</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">1</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">GiST Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">25.249</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">289.255</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">293.143</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">28.838</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">289.002</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">291.296</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">28.336</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">28.597</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">26.947</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">295.394</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">297.556</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">293.988</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">299.760</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">2</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">X PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">8.829</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">440.861</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">445.630</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">37.960</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">439.564</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">440.535</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">37.594</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">37.979</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">37.741</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">386.662</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">384.635</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">385.166</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">388.832</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">3</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">Morton PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">16.885</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">421.999</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">413.427</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">77.002</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">422.939</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">412.130</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">77.415</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">75.102</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">76.056</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">416.205</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">446.599</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">410.614</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">434.613</td></tr><tr style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right"><th style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none;text-align:right">4</th><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">Hilbert PreSort Index</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">16.824</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">417.539</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">415.962</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">56.583</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">421.226</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">414.553</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">56.320</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">55.600</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">55.338</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">416.639</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">421.243</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border:none">418.550</td><td style="box-sizing:unset;vertical-align:middle;padding:0.5em;line-height:normal;max-width:none;border: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><br></div><div>Best regards,</div><div>Han</div></div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jul 8, 2021 at 1:32 AM Giuseppe Broccolo <<a href="mailto:g.broccolo.7@gmail.com" target="_blank">g.broccolo.7@gmail.com</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 dir="ltr"><div dir="ltr">Hi Han,<br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Il giorno mer 7 lug 2021 alle ore 18:05 Han Wang <<a href="mailto:hanwgeek@gmail.com" target="_blank">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="auto"><div dir="auto">Hi Regina and hi Giuseppe,<div dir="auto"><br></div><div dir="auto">Thanks for your reply!</div><div dir="auto"><br></div><div dir="auto">I am now checking the original paper and postgres's implement of gist. And now I think the query performance test after building gist index is necessary. Because as Darafei mentioned, the fast sorting building method may just pack the sorted tuples into pages regardless of which hash function it use. The correctness of index may be checked in the runtime query. At present, I am working on confirm the io access of hash functions.</div><div dir="auto"><br></div><div dir="auto">Feel free to give suggestions or questions.</div></div></div></blockquote><div><br></div><div>The correctness of the index should be covered by some of the regression tests in action for the GiST support in PostGIS - for instance, kNN searches etc. - for the moment I am really curious about the I/O access of the hash functions. This is something maybe it has not been checked even in PostgreSQL, and maybe it would be good to share the results with them as well.</div><div><br></div><div>Keep us updated, and thank you for your help!</div><div><br></div><div>Giuseppe.<br></div></div></div>
</blockquote></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>