[postgis-users] Help with Bad Query Plan

[Oliver Snowden]

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.