[postgis-users] No index usage on geography query plan?
Nicholas Bower
nick at petangent.net
Tue May 25 04:21:00 PDT 2010
Neither of the ST_Intersects clauses below invoke index usage according to
explain output, despite docs saying they should automatically be doing bbox
on the index;
explain analyze SELECT wastac.t_swath_metadata.swath_id AS
wastac_t_swath_metadata_swath_id
FROM wastac.t_swath_metadata
JOIN wastac.t_tile_metadata ON wastac.t_swath_metadata.swath_id =
wastac.t_tile_metadata.swath_id
JOIN wastac.t_tile_geometry ON wastac.t_tile_metadata.tile_id =
wastac.t_tile_geometry.tile_id
AND wastac.t_tile_metadata.grid_id = wastac.t_tile_geometry.grid_id
WHERE wastac.t_swath_metadata.quicklook = True
AND
ST_Intersects(swath_bounding,ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'))
AND *ST_Intersects(border,*ST_GeographyFromText('SRID=4326;POLYGON((116.751709
-31.381779,116.883545 -32.676373,114.741211 -32.796510,114.796143
-31.316101,116.751709 -31.381779))'))
AND wastac.t_tile_metadata.ob_cloud_m > 2.0 GROUP BY
wastac.t_swath_metadata.swath_id
HAVING count(wastac.t_tile_metadata.tile_id) = 95 LIMIT 20
One of the above ST_Intersects uses this (just note the border
column/index):
\d wastac.t_tile_geometry
Table "wastac.t_tile_geometry"
Column | Type | Modifiers
---------+-------------------------+----------------------------------------------------------------------
tile_id | integer | not null
grid_id | integer | not null default
nextval('wastac.t_grid_type_grid_id_seq'::regclass)
* border | geography(Polygon,4326) |*
centre | geography(Point,4326) |
Indexes:
"t_tile_geometry_pkey" PRIMARY KEY, btree (tile_id, grid_id)
*"t_tile_geometry_border_key" gist (border)*
"t_tile_geometry_centre_key" gist (centre)
Foreign-key constraints:
"fk_t_tile_geometry_1" FOREIGN KEY (grid_id) REFERENCES
wastac.t_grid_type(grid_id)
Referenced by:
TABLE "wastac.t_tile_metadata" CONSTRAINT "fk_t_tile_metadata_2" FOREIGN
KEY (tile_id, grid_id) REFERENCES wastac.t_tile_geometry(tile_id, grid_id)
Yet we see no evidence of GIST index usage here:
Limit (cost=0.00..2159649.87 rows=20 width=8) (actual
time=4036.091..44743.940 rows=20 loops=1)
-> GroupAggregate (cost=0.00..1001537626.11 rows=9275 width=8) (actual
time=4036.088..44743.894 rows=20 loops=1)
Filter: (count(t_tile_metadata.tile_id) = 95)
-> Nested Loop (cost=0.00..1001387855.03 rows=29926392 width=8)
(actual time=53.319..44735.239 rows=11683 loops=1)
-> Nested Loop (cost=0.00..743388879.45 rows=29926392
width=12) (actual time=32.931..21859.582 rows=1719454 loops=1)
-> Index Scan using t_swath_metadata_pkey on
t_swath_metadata (cost=0.00..669641.79 rows=9275 width=4) (actual
time=0.765..255.304 rows=451
loops=1)
Filter: (quicklook AND
st_intersects(swath_bounding,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D4
0DE72F563935640C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography))
-> Index Scan using t_tile_metadata_pkey on
t_tile_metadata (cost=0.00..80037.21 rows=3227 width=12) (actual
time=6.403..45.723 rows=3813 l
oops=451)
Index Cond: (t_tile_metadata.swath_id =
t_swath_metadata.swath_id)
Filter: (t_tile_metadata.ob_cloud_m > 2::double
precision)
-> Index Scan using t_tile_geometry_pkey on t_tile_geometry
(cost=0.00..8.61 rows=1 width=8) (actual time=0.013..0.013 rows=0
loops=1719454)
Index Cond: ((t_tile_geometry.tile_id =
t_tile_metadata.tile_id) AND (t_tile_geometry.grid_id =
t_tile_metadata.grid_id))
*Filter: st_intersects(t_tile_geometry.border,
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F5639356
*
*
40C0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
*
Total runtime: 44744.241 ms
(14 rows)
However if I swap out ST_Intersects for &&, things start to look more
familiar (note appearance of t_tile_geometry_border_key), but paradoxically
the query is longer;
Limit (cost=46397.20..46397.42 rows=15 width=8) (actual
time=65951.363..65951.692 rows=8 loops=1)
-> HashAggregate (cost=46397.20..46397.42 rows=15 width=8) (actual
time=65951.361..65951.685 rows=8 loops=1)
Filter: (count(t_tile_metadata.tile_id) = 95)
-> Hash Join (cost=2986.26..46397.12 rows=15 width=8) (actual
time=960.682..65886.428 rows=67553 loops=1)
Hash Cond: (t_tile_metadata.swath_id =
t_swath_metadata.swath_id)
-> Nested Loop (cost=86.37..43490.92 rows=1644 width=8)
(actual time=106.668..64931.709 rows=84257 loops=1)
Join Filter: (t_tile_metadata.grid_id =
t_tile_geometry.grid_id)
-> *Index Scan using t_tile_geometry_border_key on
t_tile_geometry* (cost=0.00..28.97 rows=6 width=8) (actual
time=42.340..121.791 rows=112 l
oops=1)
Index Cond: (border &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1B430070AF5
C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
-> Bitmap Heap Scan on t_tile_metadata
(cost=86.37..7216.39 rows=1818 width=12) (actual time=10.025..578.134
rows=752 loops=112)
Recheck Cond: (t_tile_metadata.tile_id =
t_tile_geometry.tile_id)
Filter: (t_tile_metadata.ob_cloud_m > 2::double
precision)
-> Bitmap Index Scan on
t_tile_metadata_tile_id_key (cost=0.00..86.30 rows=1818 width=0) (actual
time=8.693..8.693 rows=1884 loops=11
2)
Index Cond: (t_tile_metadata.tile_id =
t_tile_geometry.tile_id)
-> Hash (cost=2897.85..2897.85 rows=163 width=4) (actual
time=853.913..853.913 rows=2564 loops=1)
-> Bitmap Heap Scan on t_swath_metadata
(cost=43.86..2897.85 rows=163 width=4) (actual time=454.656..851.229
rows=2564 loops=1)
Recheck Cond: (swath_bounding &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C0DF1
B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Filter: quicklook
-> Bitmap Index Scan on
t_swath_metadata_swath_bounding_key (cost=0.00..43.82 rows=1001 width=0)
(actual time=453.917..453.917 rows=3
574 loops=1)
Index Cond: (swath_bounding &&
'0103000020E61000000100000005000000F8C610001C305D40B24CBF44BC613FC0D6E253008C385D40DE72F563935640C
0DF1B430070AF5C40EE77280AF46540C01EFCC401F4B25C409F3BC1FEEB503FC0F8C610001C305D40B24CBF44BC613FC0'::geography)
Total runtime: 65952.140 ms
(21 rows)
What's going on - the difference in total cost above proves to me the
indexes are not being used.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100525/79565bf7/attachment.html>
More information about the postgis-users
mailing list