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
