[postgis-users] Help with Bad Query Plan

[Obe, Regina]

> -- 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);
> 


Make sure you have an index on temp_report_date field

Try rewriting the above like:

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'
 



-----------------------------------------
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.