[postgis-users] Help with Bad Query Plan

[Oliver Snowden]

Hi Paul (Ramsay), STEP 4 is exactly the same query as part 8 only I
have created an index in part 7.

Hi Mark (Cave-Ayland), please see below the EXPLAIN ANALYZE output for
STEP 3 and STEP 4.  My understanding is that the first choice of
"  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=11505) (actual
time=0.025..0.028 rows=1 loops=1)" is where things go badly wrong.  Cheers.

-- EXPLAIN ANALYZE STEP 3 (select geography, e.g. Asia continent)
-- 275 rows, 26ms.
EXPLAIN ANALYZE 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;

"Nested Loop  (cost=0.00..1434.38 rows=250 width=8) (actual
time=0.080..8.438 rows=275 loops=1)"
"  ->  Index Scan using gdors_geography_pkey on gdors_geography
selected_geography  (cost=0.00..8.27 rows=1 width=0) (actual
time=0.021..0.023 rows=1 loops=1)"
"        Index Cond: (gid = 3)"
"  ->  Nested Loop  (cost=0.00..1423.61 rows=250 width=8) (actual
time=0.042..7.580 rows=275 loops=1)"
"        ->  Seq Scan on gdors_geolink geolink  (cost=0.00..205.00
rows=250 width=8) (actual time=0.023..2.868 rows=275 loops=1)"
"              Filter: ((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.86 rows=1 width=4) (actual time=0.006..0.008
rows=1 loops=275)"
"              Index Cond: (geography.gid = geolink.geom_id)"
"Total runtime: 8.919 ms"


-- EXPLAIN ANALYZE STEP 4 (intersect Asia continent over all other geographies)
-- 81 rows, ~13000ms - a large increase in query time.
EXPLAIN ANALYZE 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);


"Nested Loop  (cost=0.00..1435.63 rows=1 width=8) (actual
time=100.841..17115.944 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=11505) (actual
time=0.025..0.028 rows=1 loops=1)"
"        Index Cond: (gid = 3)"
"  ->  Nested Loop  (cost=0.00..1423.61 rows=250 width=11513) (actual
time=0.045..15.803 rows=275 loops=1)"
"        ->  Seq Scan on gdors_geolink geolink  (cost=0.00..205.00
rows=250 width=8) (actual time=0.024..4.368 rows=275 loops=1)"
"              Filter: ((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.86 rows=1 width=11509) (actual
time=0.019..0.028 rows=1 loops=275)"
"              Index Cond: (geography.gid = geolink.geom_id)"
"Total runtime: 17116.900 ms"

See below for my original plan without the line breaks,

                                  project
                              geolink.report_id
                                     ¦
          intersect (geography.the_geom, selected_geography.the_geom)
                                     ¦
                ----------------------------------------
                ¦                                    ¦
              project                             project
geolink.report_id, geography.the_geom     selected_geography.the_geom
                ¦                                    ¦
               join                              restrict
    geolink.geom_id = geography.        selected_geography.gid = 3
                ¦                                      ¦
   ---------------------------       [geography as selected_geography]
   ¦                         ¦
 project                   project
report_id, geom_id      gid, the_geom
   ¦                         ¦
 restrict                    ¦
based on date                 ¦
   ¦                         ¦
 [geolink]                [geography]