Oliver, Did you try the && instead of ST_Intersects. That would help confirm if its an issue with && or _ST_Intersects.
What is strange is that in all the plans, it looks like its doing the right thing. Did you change your postgresql.conf or is it still at its default settings. If still at its default you could just be IO bound and need to up those settings. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Oliver Snowden Sent: Friday, January 09, 2009 12:44 PM To: [email protected] Subject: RE: [postgis-users] Help with Bad Query Plan Hi Mark/Regina, I have installed PostgreSQL 8.2. Unfortunately that is still slow. I am not sure how difficult it is for you to recreate the database but I have embedded some Java code to create some sample report data, should you want to/have time. 10000 refers to the report entries to make, [21619] + 1 refers to the number of geometries. All the best, Oliver. -- Installed PostgreSQL 8.2.9-1 -- Query did not finish 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..91.85 rows=1 width=8)" " -> 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=3470)" " Index Cond: (gid = 3)" " -> Index Scan using gdors_geography_the_geom on gdors_geography geography (cost=0.00..8.27 rows=1 width=3474)" " Index Cond: (selected_geography.the_geom && geography.the_geom)" " Filter: (selected_geography.the_geom && geography.the_geom)" " -> Index Scan using gdors_geolink_pkey on gdors_geolink geolink (cost=0.00..75.28 rows=1 width=8)" " Index Cond: (geolink.geom_id = geography.gid)" " Filter: ((temp_report_date >= '2008-01-06'::date) AND (temp_report_date <= '2009-01-06'::date))" -- Query with && - at least we get a result...although slower than without the spatial index. -- 81 rows, ~21000ms. 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 selected_geography.the_geom && geography.the_geom; "Hash Join (cost=16.61..231.58 rows=1 width=8)" " Hash Cond: (geolink.geom_id = geography.gid)" " -> Seq Scan on gdors_geolink geolink (cost=0.00..214.00 rows=257 width=8)" " Filter: ((temp_report_date >= '2008-01-06'::date) AND (temp_report_date <= '2009-01-06'::date))" " -> Hash (cost=16.56..16.56 rows=4 width=4)" " -> Nested Loop (cost=0.00..16.56 rows=4 width=4)" " -> Index Scan using gdors_geography_pkey on gdors_geography selected_geography (cost=0.00..8.27 rows=1 width=3470)" " Index Cond: (gid = 3)" " -> Index Scan using gdors_geography_the_geom on gdors_geography geography (cost=0.00..8.27 rows=1 width=3474)" " Index Cond: (selected_geography.the_geom && geography.the_geom)" " Filter: (selected_geography.the_geom && geography.the_geom)" Sample report data: package samplereportdata; import java.text.SimpleDateFormat; import java.util.Date; // quick hack public class Main { private static void getSample() { java.util.Random r = new java.util.Random(); long timeNow = java.util.Calendar.getInstance().getTimeInMillis(); Date date = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String sDate = ""; for (int i = 0; i < 10000; i++) { int j = r.nextInt(21619) + 1; r.nextLong(); date.setTime(new Float(timeNow * r.nextFloat()).longValue()); sDate = sdf.format(date); System.out.println("INSERT INTO gdors_geolink(report_id, geom_id, lastupdated, temp_report_date) VALUES ("+(i+1)+","+j+",'2006-06-01', '"+sDate +"');"); } } /** * @param args the command line arguments */ public static void main(String[] args) { getSample(); } } _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
