[postgis-users] speed up spatial queries (distance() or intersects() or ???)

[Daniel Faivre]

Hello,

I'm fighting hard to optimize spatial queries, and i dream about 
astonishing performance increasements reported by some peoples in this 
list ;-)

I've got two spatial tables, properly indexed, and I want to make a 
"buffer request", eg retrieving features from table1 included in a 
selected feature from table2.
All geom fields, search fields, and oid fields are indexed. The 
subselect only return one tuple, a MULTIPOLYGON.

My best result was with this query:

SELECT oid AS qoid FROM lg_annee WHERE  lga_annee='2003' and 
lga_nat_cdn=2 AND lga_geom && box3d((SELECT
buf_geom FROM buffer WHERE type='zrb' )) AND distance(lga_geom, (SELECT 
buf_geom FROM buffer WHERE type='zrb' )) = 0;

NOTICE:  QUERY PLAN:

Index Scan using lga_geom_ndx on lg_annee  (cost=0.00..6.03 rows=1 
width=4) (actual time=11.41..97363.32 rows=5150 loops=1)
 InitPlan
   ->  Index Scan using buf_type_ndx on buffer  (cost=0.00..5.78 rows=1 
width=32) (actual time=0.18..0.18 rows=1 loops=1)
   ->  Index Scan using buf_type_ndx on buffer  (cost=0.00..5.78 rows=1 
width=32) (actual time=0.05..0.06 rows=1 loops=1)
Total runtime: 97390.00 msec

Using ENABLE_SEQSCAN=off slow down my requests, and using the new 
postgis 0.8 function intersects too (unfortunately: i've hopped than a 
boolean operator could be faster than distance(), but it is slower ...).

Is anybody see an idea ? Does "PostGIS guru'z" could give me a feedback 
about average time for such requests ?

Thanks by advance,

-- 
Daniel Faivre
Ohceo - Camptocamp
04 79 75 28 19
180 rue du Genevois
73000 Chambéry le Haut