Hi,
I think your query is not well-formed as required.
st_intersects() should be in your "clause where" not afer "select
"...that' why the index is not used
then a seq scan is used...For me it is normal.
intersect is tested for each tuple
Have a look in the following examples and see the required times
routing_db=# CREATE INDEX nom_idx ON communes_lr(nom);
CREATE INDEX
routing_db=# VACUUM FULL ANALYZE communes_lr ;
VACUUM
routing_db=# explain analyze select count(*) from communes_lr where
nom = 'MONTPELLIER';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=8.27..8.28 rows=1 width=0) (actual
time=0.152..0.154 rows=1 loops=1)
-> Index Scan using nom_idx on communes_lr (cost=0.00..8.27
rows=1 width=0) (actual time=0.136..0.140 rows=1 loops=1)
Index Cond: ((nom)::text = 'MONTPELLIER'::text)
Total runtime: 0.239 ms
(4 lignes)
routing_db=# explain analyze select
st_intersects(a.the_geom,b.the_geom) from communes_lr a,fr
routing_db=# explain analyze select
st_intersects(a.the_geom,b.the_geom) from communes_lr a,fra3_nw b
where a.nom='MONTPELLIER';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..65645.32 rows=998282 width=4114) (actual
time=0.694..31988.934 rows=998282 loops=1)
-> Index Scan using nom_idx on communes_lr a (cost=0.00..8.27
rows=1 width=3953) (actual time=0.130..0.133 rows=1 loops=1)
Index Cond: ((nom)::text = 'MONTPELLIER'::text)
-> Seq Scan on fra3_nw b (cost=0.00..50662.82 rows=998282
width=161) (actual time=0.269..2860.765 rows=998282 loops=1)
Total runtime: 33204.436 ms
(5 lignes)
routing_db=# explain analyze select
st_intersects(a.the_geom,b.the_geom) from communes_lr a,fra3_nw b
where a.nom='MONTPELLIER' and st_intersects(a.the_geom,b.the_geom);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..33.99 rows=213 width=4114) (actual
time=75.267..34830.122 rows=14383 loops=1)
Join Filter: _st_intersects(a.the_geom, b.the_geom)
-> Index Scan using nom_idx on communes_lr a (cost=0.00..8.27
rows=1 width=3953) (actual time=0.175..0.178 rows=1 loops=1)
Index Cond: ((nom)::text = 'MONTPELLIER'::text)
-> Index Scan using fra3_nw_the_geom_gist on fra3_nw b
(cost=0.00..24.59 rows=5 width=161) (actual time=72.415..12128.147
rows=20747 loops=1)
Index Cond: (a.the_geom && b.the_geom)
Filter: (a.the_geom && b.the_geom)
Total runtime: 34885.910 ms
(8 lignes)
routing_db=# explain analyze select count(b.*) from communes_lr
a,fra3_nw b where a.nom='MONTPELLIER' and
st_intersects(a.the_geom,b.the_geom);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=33.46..33.47 rows=1 width=32) (actual
time=14471.698..14471.700 rows=1 loops=1)
-> Nested Loop (cost=0.00..32.93 rows=213 width=32) (actual
time=8.955..14421.733 rows=14383 loops=1)
Join Filter: _st_intersects(a.the_geom, b.the_geom)
-> Index Scan using nom_idx on communes_lr a
(cost=0.00..8.27 rows=1 width=3953) (actual time=0.125..0.128 rows=1
loops=1)
Index Cond: ((nom)::text = 'MONTPELLIER'::text)
-> Index Scan using fra3_nw_the_geom_gist on fra3_nw b
(cost=0.00..24.59 rows=5 width=193) (actual time=7.341..1883.205
rows=20747 loops=1)
Index Cond: (a.the_geom && b.the_geom)
Filter: (a.the_geom && b.the_geom)
Total runtime: 14471.823 ms
(9 lignes)
Quoting danny <[EMAIL PROTECTED]>:
Hello,
I'm wondering if it's normal to have a 420 second response time for the
following query.
A spatial index has been set on the spatial field (the_geom) and other
important fields (like sitecode). I've generously tweaked the memory options
for postgresql.
With such a response time I would have to let my query run for half a year
before getting the answer I'm interested in! :)
Anybody know how I can boost up the process or is it doomed to always be so
slow? The europe_layer is indeed a complex polygon....
select st_intersects(a.the_geom,b.the_geom) from sites a, europe_waters b
where a.sitecode = 'xxxx';
"Nested Loop (cost=0.00..496.18 rows=1310 width=35786)"
" -> Seq Scan on sites a (cost=0.00..453.43 rows=1 width=35754)"
" Filter: ((sitecode)::text = 'xxxx'::text)"
" -> Seq Scan on europe_waters b (cost=0.00..23.10 rows=1310 width=32)"
My true objective would be to join thses two tables through an st_intersects
but for the time it is unconceivable.
Many thanks,
Erik
===================
Jean David TECHER
06 60 46 85 05
04 99 77 17 87
===================
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
