[postgis-users] Help with Bad Query Plan
mark.cave-ayland at siriusit.co.uk
Sat Jan 10 02:42:56 PST 2009
Oliver Snowden wrote:
> 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,  + 1 refers to the number of geometries.
> All the best, Oliver.
> -- 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
> 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
> " 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 &&
> " Filter: (selected_geography.the_geom &&
Please can we see the EXPLAIN ANALYZE rather then just EXPLAIN of the
above query? This will give extra information showing where the time is
being spent during query execution.
Sirius Corporation - The Open Source Experts
T: +44 870 608 0063
More information about the postgis-users