What happens if you leave out ST_Intersects and just use && instead of doing ST_Intersects?
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Oliver Snowden Sent: Friday, January 09, 2009 10:22 AM To: [email protected] 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 [email protected] 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. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
