[postgis-users] ST_Intersects

[Jean David TECHER]

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 <whatevar89 at gmail.com>:

> 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
===================