<div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">What does the output of:<br>
<br>
SELECT version(), postgis_full_version();<br>
<br>
return?<br></blockquote><div><br></div><div>PostgreSQL 8.4.3 on x86_64-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit | POSTGIS="1.5.1" GEOS="3.2.1-CAPI-1.6.1" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.6.23" USE_STATS</div>
<div><br></div><div>I went back to the pre-geography verison of the table that still had geometries (SRID=-1) and it's a different story with ST_Intersects.  Quite clearly there's a problem here - ST_Intersects printing out index usage fine when geometries are used.</div>
<div><br></div><div><div>explain analyze select count(*) from wastac.t_tile_geometry_old where ST_Intersects(border, ST_GeometryFromText('POLYGON((116.751709 -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143 -31.316101,116.751709 -31.381779))', -1));</div>
<div><br></div><div> Aggregate  (cost=256.30..256.31 rows=1 width=0) (actual time=81.997..81.998 rows=1 loops=1)   ->  Bitmap Heap Scan on t_tile_geometry_old  (cost=5.09..256.25 rows=20 width=0) (actual time=76.303..81.950 rows=96 loops=1)</div>
<div>         Recheck Cond: (border && '01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1</div><div>B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)</div>
<div>         Filter: _st_intersects(border, '01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F56393564</div><div>0C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)</div>
<div>         ->  Bitmap Index Scan on t_tile_geometry_old_border_key  (cost=0.00..5.09 rows=61 width=0) (actual time=0.307..0.307 ro</div><div>ws=100 loops=1)</div><div>               Index Cond: (border && '01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C</div>
<div>0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)</div><div> Total runtime: 82.107 ms</div><div><br></div><div>But simply swapping the query region above from geometry to geography we're back to no index usage,</div>
<div><br></div><div><div>explain analyze select count(*) from wastac.t_tile_geometry_old where ST_Intersects(border, ST_GeographyFromText('SRID=4316;POLYGON((116.751709 -31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143 -31.316101,116.751709 -31.381779))'));</div>
<div><br></div><div> Aggregate  (cost=362164.77..362164.78 rows=1 width=0) (actual time=80302.237..80302.237 rows=1 loops=1)   ->  Seq Scan on t_tile_geometry_old  (cost=0.00..361101.11 rows=425463 width=0) (actual time=19680.252..80302.172 rows=95 loo</div>
<div>ps=1)</div><div>         Filter: st_intersects((border)::geography, '0103000020DC1000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008</div><div>C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra</div>
<div>phy)</div><div> Total runtime: 80302.285 ms</div><div><br></div></div><div><br></div><div><br></div><div><div> \d wastac.t_tile_geometry_old</div><div>Table "wastac.t_tile_geometry_old"</div><div> Column  |   Type   | Modifiers</div>
<div>---------+----------+-----------</div><div> tile_id | integer  | not null</div><div> grid_id | integer  | not null</div><div> centre  | geometry |</div><div> border  | geometry |</div><div>Indexes:</div><div>    "t_tile_geometry_old_border_key" gist (border)</div>
<div><br></div></div><div><br></div><div><br></div></div><div> </div></div>