>
> 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)
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
