I just tried to load up the geometry portion of the problem only, and I am *not* seeing the same effect. Does the geometry-only part of the query also exhibit the long query time for you?
select count(*) from gdors_geography g1, gdors_geography g2 where st_intersects(g1.the_geom, g2.the_geom) and g2.gid = 3; With an index this takes me 9s and without it takes 15s (hooray, prepared geometry, this would probably have been a multi-minute problem in the good old days). If you're I/O bound that *could* be part of the problem, however, for something like Asia. It's a big geometry, it's stored in toast tuples, if it's too big to cache, it would be a lot of work to haul it out over and over and over again. Still, that should pertain for the non-indexed cases too. Is the index forcing a full spatial table join to happen before the individual record is pulled (I can't read plans, it's tragic)? P. On Fri, Jan 9, 2009 at 10:45 AM, Paragon Corporation <[email protected]> wrote: > 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 > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
