<div>Well the index says it is being used, however I'm still quite suspicious because of performance results below.</div><div><br></div><div>I attach 3 versions of a simply query (Geography ST_Intersects, Geometry ST_Intersects, Geography &&) which is a simple square ROI intersection over 150k rows, each having a single polygon around 50-80 verticies.</div>
<div><br></div><div>1) Geography ST_Intersects gives 13s :-(</div><div><br></div><div><div>wastac=# explain analyze SELECT count(1) AS count_1</div><div>wastac-# FROM wastac.t_swath_metadata</div><div>wastac-# WHERE wastac.t_swath_metadata.quicklook = True</div>
<div>wastac-# AND ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((104.765625 -39.0277188402,131.30859375 -39.0277188402,131.30859375 -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))'));</div>
<div><br></div><div> Aggregate (cost=13556.17..13556.18 rows=1 width=0) (actual time=12886.056..12886.057 rows=1 loops=1)</div><div> -> Bitmap Heap Scan on t_swath_metadata (cost=506.07..13554.65 rows=607 width=0) (actual time=17.168..12883.162 rows=8462 loops=1)</div>
<div> Recheck Cond: (swath_bounding && '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00</div><div>0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</div>
<div> Filter: (quicklook AND (_st_distance(swath_bounding, '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E0696</div><div>0402F127C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography, 0::double pre</div>
<div>cision, false) < 1e-05::double precision))</div><div> -> Bitmap Index Scan on t_swath_metadata_swath_bounding_key (cost=0.00..505.91 rows=10347 width=0) (actual time=8.148..8.148 rows=1</div><div>4261 loops=1)</div>
<div> Index Cond: (swath_bounding && '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834</div><div>3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</div>
<div> Total runtime: 12886.287 ms</div><div>(7 rows)</div><div><br></div><div><br></div><div>2) Geometry ST_Intersects gives <1s :-)</div><div><br></div><div><div>wastac=# explain analyze SELECT count(1) AS count_1</div>
<div>wastac-# FROM wastac.t_swath_metadata_old</div><div>wastac-# WHERE quicklook = True</div><div>wastac-# AND ST_Intersects(swath_bounding,ST_GeomFromText('POLYGON((104.765625 -39.0277188402,131.30859375 -39.0277188402,131.30859375 -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))', -1));</div>
<div><br></div><div> Aggregate (cost=9505.13..9505.14 rows=1 width=0) (actual time=95.681..95.682 rows=1 loops=1) -> Bitmap Heap Scan on t_swath_metadata_old (cost=506.77..9503.27 rows=745 width=0) (actual time=4.198..93.366 rows=7274 loops=1)</div>
<div> Recheck Cond: (swath_bounding && '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000000E</div><div>0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)</div>
<div> Filter: (quicklook AND _st_intersects(swath_bounding, '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F12</div><div>7C4A8C8343C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry))</div>
<div> -> Bitmap Index Scan on t_swath_metadata_old_swath_bounding_key (cost=0.00..506.58 rows=16840 width=0) (actual time=3.557..3.557 ro</div><div>ws=9020 loops=1)</div><div> Index Cond: (swath_bounding && '010300000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C000000</div>
<div>000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geometry)</div><div> Total runtime: 95.757 ms</div><div>(7 rows)</div><div><br></div></div><div><br></div><div>3) Geography bounding box < <1s:</div>
<div><br></div><div>wastac=# explain analyze SELECT count(1) AS count_1</div><div>wastac-# FROM wastac.t_swath_metadata</div><div>wastac-# WHERE wastac.t_swath_metadata.quicklook = True</div><div>wastac-# AND swath_bounding && ST_GeographyFromText('SRID=4326;POLYGON((104.765625 -39.0277188402,131.30859375 -39.0277188402,131.30859375 -15.7076627696,104.765625 -15.7076627696,104.765625 -39.0277188402))');</div>
<div><br></div><div> Aggregate (cost=10948.03..10948.04 rows=1 width=0) (actual time=30.583..30.584 rows=1 loops=1) -> Bitmap Heap Scan on t_swath_metadata (cost=506.38..10943.48 rows=1820 width=0) (actual time=8.884..27.786 rows=9806 loops=1)</div>
<div> Recheck Cond: (swath_bounding && '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C8343C00</div><div>0000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</div>
<div> Filter: quicklook</div><div> -> Bitmap Index Scan on t_swath_metadata_swath_bounding_key (cost=0.00..505.92 rows=10348 width=0) (actual time=7.403..7.403 rows=1</div><div>4263 loops=1)</div><div>
Index Cond: (swath_bounding && '0103000020E610000001000000050000000000000000315A402F127C4A8C8343C000000000E06960402F127C4A8C834</div><div>3C000000000E0696040BD0E48C6526A2FC00000000000315A40BD0E48C6526A2FC00000000000315A402F127C4A8C8343C0'::geography)</div>
<div> Total runtime: 30.637 ms</div><div>(7 rows)</div></div><div><br></div><br><br><div class="gmail_quote">On 28 May 2010 16:31, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Okay I think the fix is a really simple
one</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">Change your ST_Intersects function to this and see if it
behaves right </font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">CREATE OR REPLACE FUNCTION st_intersects(geography,
geography)<br> RETURNS boolean AS<br>'SELECT $1 && $2 AND
_ST_Distance($1, $2, 0.0, false) < 0.00001'<br> LANGUAGE 'sql'
IMMUTABLE<br> COST 100;</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial">It should no longer need the && help to use the
index.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" size="2" face="Arial"></font></span> </div></div></blockquote></div><br>