<html><head><style>body{font-family:Helvetica,Arial;font-size:13px}</style></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;"><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">OK, I finally have an actually proof of the concept of using spgist with geometry. This only works with a patched postgresql from Oleg & Teodor that includes a ‘compress’ option for spgist. This feature will only arrive in PostgreSQL at 9.5, so it’s going to be a long wait for real spgist and geometry. My fault for not testing spgist more/sooner when it first arrived.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Since kd-trees make the most sense for points, the index build for this opclass cans out if you try and build against some other object. It would be possible to set up a quad-tree that could handle all object types, using a fifth node on each inner node to hold the ‘boundary crossers’. If the number of boundary crossers was sufficiently high, though, the index efficiency could be affected.</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Here’s the postgresql branch</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">https://github.com/pramsey/postgres/tree/spgistcompress</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Here’s the postgis branch (you have to manually load the gserialized_spgist_2d.sql file to get the bindings)</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">https://github.com/pramsey/postgis/tree/spgist-kdtree</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">Here’s what it looks like running,</div><div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;"><br></div><div id="bloop_customfont" style="margin: 0px;"><div id="bloop_customfont" style="margin: 0px;">pramsey=# CREATE TABLE somepoints AS </div><div id="bloop_customfont" style="margin: 0px;">pramsey-# SELECT </div><div id="bloop_customfont" style="margin: 0px;">pramsey-# generate_series AS id,</div><div id="bloop_customfont" style="margin: 0px;">pramsey-# st_setsrid(st_makepoint(random()*10000, random()*10000),26910) AS geom</div><div id="bloop_customfont" style="margin: 0px;">pramsey-# FROM generate_series(1,1000000);</div><div id="bloop_customfont" style="margin: 0px;">SELECT 1000000</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">pramsey=# EXPLAIN ANALYZE </div><div id="bloop_customfont" style="margin: 0px;">pramsey-# SELECT * FROM somepoints </div><div id="bloop_customfont" style="margin: 0px;">pramsey-# WHERE 'LINESTRING(5898 7990, 6198 8290)'::geometry && geom;</div><div id="bloop_customfont" style="margin: 0px;"> QUERY PLAN </div><div id="bloop_customfont" style="margin: 0px;">--------------------------------------------------------------------------------------------------------------------</div><div id="bloop_customfont" style="margin: 0px;"> Seq Scan on somepoints (cost=0.00..26161.62 rows=253626 width=36) (actual time=0.625..543.586 rows=894 loops=1)</div><div id="bloop_customfont" style="margin: 0px;"> Filter: ('01020000000200000000000000000AB740000000000036BF40000000000036B840000000000031C040'::geometry && geom)</div><div id="bloop_customfont" style="margin: 0px;"> Rows Removed by Filter: 999106</div><div id="bloop_customfont" style="margin: 0px;"> Planning time: 0.107 ms</div><div id="bloop_customfont" style="margin: 0px;"> Execution time: 543.779 ms</div><div id="bloop_customfont" style="margin: 0px;">(5 rows)</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">pramsey=# CREATE INDEX splx ON somepoints using spgist (geom spgist_geometry_kdtree_ops); </div><div id="bloop_customfont" style="margin: 0px;">CREATE INDEX</div><div id="bloop_customfont" style="margin: 0px;"><br></div><div id="bloop_customfont" style="margin: 0px;">pramsey=# EXPLAIN ANALYZE </div><div id="bloop_customfont" style="margin: 0px;"> SELECT * FROM somepoints </div><div id="bloop_customfont" style="margin: 0px;"> WHERE 'LINESTRING(5898 7990, 6198 8290)'::geometry && geom;</div><div id="bloop_customfont" style="margin: 0px;"> QUERY PLAN </div><div id="bloop_customfont" style="margin: 0px;">------------------------------------------------------------------------------------------------------------------------------</div><div id="bloop_customfont" style="margin: 0px;"> Bitmap Heap Scan on somepoints (cost=6734.28..19544.28 rows=200000 width=36) (actual time=0.870..7.955 rows=894 loops=1)</div><div id="bloop_customfont" style="margin: 0px;"> Recheck Cond: ('01020000000200000000000000000AB740000000000036BF40000000000036B840000000000031C040'::geometry && geom)</div><div id="bloop_customfont" style="margin: 0px;"> Heap Blocks: exact=851</div><div id="bloop_customfont" style="margin: 0px;"> -> Bitmap Index Scan on splx (cost=0.00..6684.28 rows=200000 width=0) (actual time=0.571..0.571 rows=894 loops=1)</div><div id="bloop_customfont" style="margin: 0px;"> Index Cond: ('01020000000200000000000000000AB740000000000036BF40000000000036B840000000000031C040'::geometry && geom)</div><div id="bloop_customfont" style="margin: 0px;"> Planning time: 0.230 ms</div><div id="bloop_customfont" style="margin: 0px;"> Execution time: 8.112 ms</div><div id="bloop_customfont" style="margin: 0px;">(7 rows)</div><div id="bloop_customfont" style="color: rgb(0, 0, 0); font-family: Helvetica, Arial; font-size: 13px; margin: 0px;"><br></div></div><br><div class="bloop_sign" id="bloop_sign_1417458640644884992">
<title></title>
<div>
<br>
</div>
-- <br>
<b>Paul Ramsey</b>
<div>Senior Strategist / Evangelist | <a href="http://boundlessgeo.com">Boundless</a>
<div>pramsey@boundlessgeo.com
</div>
<div>250-885-0632
</div>
<div>@boundlessgeo
</div>
<div><img src="http://boundlessgeo.com/wp-content/uploads/files/boundless_sig.png"></div>
</div>
</div></body></html>