[postgis-users] No index usage on geography query plan?

[Nicholas Bower]

>
> What does the output of:
>
> SELECT version(), postgis_full_version();
>
> return?
>

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

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.

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));

 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)
         Recheck Cond: (border &&
'01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1
B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
         Filter: _st_intersects(border,
'01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F56393564
0C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
         ->  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
ws=100 loops=1)
               Index Cond: (border &&
'01030000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C
0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geometry)
 Total runtime: 82.107 ms

But simply swapping the query region above from geometry to geography we're
back to no index usage,

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))'));

 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
ps=1)
         Filter: st_intersects((border)::geography,
'0103000020DC1000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008
C385D40DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geogra
phy)
 Total runtime: 80302.285 ms



 \d wastac.t_tile_geometry_old
Table "wastac.t_tile_geometry_old"
 Column  |   Type   | Modifiers
---------+----------+-----------
 tile_id | integer  | not null
 grid_id | integer  | not null
 centre  | geometry |
 border  | geometry |
Indexes:
    "t_tile_geometry_old_border_key" gist (border)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100526/9d26e01f/attachment.html>