[postgis-users] Help with Bad Query Plan

[Mark Cave-Ayland]

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, [21619] + 1 refers to the number of geometries.
> 
> All the best, Oliver.

(cut)

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

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.


ATB,

Mark.

-- 
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063