[postgis-users] Help with Bad Query Plan
Obe, Regina
robe.dnd at cityofboston.gov
Fri Jan 9 07:37:39 PST 2009
What happens if you leave out ST_Intersects and just use
&& instead of doing ST_Intersects?
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Oliver Snowden
Sent: Friday, January 09, 2009 10:22 AM
To: postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] Help with Bad Query Plan
Thanks Mark/Regina for the quick replies. I hope to install 8.2
sometime
today, just for comparison purposes. Please see my database notes,
below.
I have put an index on the date field, for all to see, although I did
index
every field, yesterday, just to see. Cheers.
-- 5.1
CREATE INDEX gdors_geolink_temp_report_date_index
ON gdors_geolink USING btree (temp_report_date);
VACUUM ANALYZE;
-- STEP 6 (continuing from STEP 5)
-- 81 rows, ~17000ms.
SELECT geolink.report_id, geography.gid
FROM gdors_geolink geolink, gdors_geography geography, gdors_geography
selected_geography
WHERE geolink.temp_report_date BETWEEN '2008-01-06' AND '2009-01-06'
AND geolink.geom_id = geography.gid
AND selected_geography.gid=3
AND ST_Intersects(selected_geography.the_geom, geography.the_geom);
-- EXPLAIN ANALYZE for STEP 6.
"Nested Loop (cost=6.94..1340.07 rows=1 width=8) (actual
time=161.040..17182.337 rows=81 loops=1)"
" Join Filter: ((selected_geography.the_geom && geography.the_geom) AND
_st_intersects(selected_geography.the_geom, geography.the_geom))"
" -> Index Scan using gdors_geography_pkey on gdors_geography
selected_geography (cost=0.00..8.27 rows=1 width=14676) (actual
time=0.068..0.071 rows=1 loops=1)"
" Index Cond: (gid = 3)"
" -> Nested Loop (cost=6.94..1327.87 rows=262 width=14684) (actual
time=0.250..18.934 rows=275 loops=1)"
" -> Bitmap Heap Scan on gdors_geolink geolink
(cost=6.94..65.87
rows=262 width=8) (actual time=0.225..1.563 rows=275 loops=1)"
" Recheck Cond: ((temp_report_date >= '2008-01-06'::date)
AND
(temp_report_date <= '2009-01-06'::date))"
" -> Bitmap Index Scan on
gdors_geolink_temp_report_date_index
(cost=0.00..6.87 rows=262 width=0) (actual time=0.200..0.200 rows=275
loops=1)"
" Index Cond: ((temp_report_date >=
'2008-01-06'::date)
AND (temp_report_date <= '2009-01-06'::date))"
" -> Index Scan using gdors_geography_pkey on gdors_geography
geography (cost=0.00..4.80 rows=1 width=14680) (actual
time=0.038..0.048
rows=1 loops=275)"
" Index Cond: (geography.gid = geolink.geom_id)"
"Total runtime: 17183.370 ms"
-- 7. Create Index:
CREATE INDEX gdors_geography_geom_index ON gdors_geography USING GIST
(the_geom);
VACUUM ANALYZE;
-- 8. Execute intersect query.
-- EXECUTES FOR A VERY LONG TIME - now with the date index.
SELECT geolink.report_id, geography.gid
FROM gdors_geolink geolink, gdors_geography geography, gdors_geography
selected_geography
WHERE geolink.temp_report_date BETWEEN '2008-01-06' AND '2009-01-06'
AND geolink.geom_id = geography.gid
AND selected_geography.gid=3
AND ST_Intersects(selected_geography.the_geom, geography.the_geom);
-- For 8, used 'Explain query' in pgAdminIII to get result. EXPLAIN
ANALYZE
too slow - it would not finish within hours? days?
"Nested Loop (cost=0.00..26.39 rows=1 width=8)"
" Join Filter: (geography.gid = geolink.geom_id)"
" -> Nested Loop (cost=0.00..16.56 rows=1 width=4)"
" Join Filter: _st_intersects(selected_geography.the_geom,
geography.the_geom)"
" -> Index Scan using gdors_geography_pkey on gdors_geography
selected_geography (cost=0.00..8.27 rows=1 width=11763)"
" Index Cond: (gid = 3)"
" -> Index Scan using gdors_geography_geom_index on
gdors_geography
geography (cost=0.00..8.27 rows=1 width=11767)"
" Index Cond: (selected_geography.the_geom &&
geography.the_geom)"
" Filter: (selected_geography.the_geom &&
geography.the_geom)"
" -> Index Scan using gdors_geolink_temp_report_date_index on
gdors_geolink geolink (cost=0.00..6.64 rows=255 width=8)"
" Index Cond: ((geolink.temp_report_date >= '2008-01-06'::date)
AND
(geolink.temp_report_date <= '2009-01-06'::date))"
-- 8b. Rewrote the query as Regina (Obe) stated in e-mail dated
2009-01-09
13:10.
-- EXECUTES FOR A VERY LONG TIME (hours? days?) - it hasn't ever
finished
for me.
SELECT geolink.report_id, geography.gid
FROM gdors_geolink AS geolink INNER JOIN gdors_geography AS geography ON
geolink.geom_id = geography.gid INNER JOIN (SELECT gid, the_geom FROM
gdors_geography WHERE gid = 3) AS selected_geography ON
ST_Intersects(selected_geography.the_geom, geography.the_geom)
WHERE geolink.temp_report_date BETWEEN '2008-01-06' AND '2009-01-06';
-- For 8b, used 'Explain query' in pgAdminIII to get result. EXPLAIN
ANALYZE too slow - it would not finish within hours? days?
"Nested Loop (cost=0.00..26.13 rows=1 width=8)"
" Join Filter: (geography.gid = geolink.geom_id)"
" -> Nested Loop (cost=0.00..16.56 rows=1 width=4)"
" Join Filter: _st_intersects(gdors_geography.the_geom,
geography.the_geom)"
" -> Index Scan using gdors_geography_pkey on gdors_geography
(cost=0.00..8.27 rows=1 width=16609)"
" Index Cond: (gid = 3)"
" -> Index Scan using gdors_geography_geom_index on
gdors_geography
geography (cost=0.00..8.27 rows=1 width=16613)"
" Index Cond: (gdors_geography.the_geom &&
geography.the_geom)"
" Filter: (gdors_geography.the_geom && geography.the_geom)"
" -> Index Scan using gdors_geolink_temp_report_date_index on
gdors_geolink geolink (cost=0.00..6.47 rows=248 width=8)"
" Index Cond: ((geolink.temp_report_date >= '2008-01-06'::date)
AND
(geolink.temp_report_date <= '2009-01-06'::date))"
-- Note: did not use a.the_geom && b.the_geom notation as not required
in
version 1.3 and above. Source:
http://www.bostongis.com/postgis_intersections.snippet.
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
More information about the postgis-users
mailing list