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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
