[postgis-users] Strange result about spatial indexes?

[Jose Carlos]

Hi everyone,
I have one table, with around 25000 polygons.
Why the predicate (st_expand(p1.geom,0) && p2.geom) takes 6.5seg and the
predicate p1.geom && p2.geom takes 203seg? I though the predicate
(st_expand(p1.geom,0) && p2.geom) should be a little bit slower than
p1.geom && p2.geom though.


Thanx to all,
cheers,
Jose carlos



test3=# explain analyze select count(*) from suelos1 p1, suelos1 p2
where (st_expand(p1.geom,0) && p2.geom);
                                                                   QUERY
PLAN                                
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19540.00..19540.01 rows=1 width=0) (actual
time=6554.345..6554.347 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..19531.88 rows=3248 width=0) (actual
time=0.178..6158.890 rows=251009 loops=1)
         ->  Seq Scan on suelos1 p1  (cost=0.00..2491.87 rows=25487
width=5617) (actual time=0.012..48.288 rows=25487 loops=1)
         ->  Index Scan using suelos_geom_idx on suelos1 p2 
(cost=0.00..0.65 rows=1 width=5617) (actual time=0.107..0.197 rows=10
loops=25487)
               Index Cond: (st_expand(p1.geom, 0::double precision) &&
p2.geom)
 Total runtime: 6554.427 ms
(6 rows)

test3=# explain analyze select count(*) from suelos1 p1, suelos1 p2
where (p1.geom && p2.geom);
                                                                   QUERY
PLAN                                
------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=19659.32..19659.33 rows=1 width=0) (actual
time=203766.354..203766.356 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..19404.44 rows=101948 width=0) (actual
time=0.158..203312.648 rows=251009 loops=1)
         ->  Seq Scan on suelos1 p1  (cost=0.00..2491.87 rows=25487
width=5617) (actual time=0.010..60.117 rows=25487 loops=1)
         ->  Index Scan using suelos_geom_idx on suelos1 p2 
(cost=0.00..0.65 rows=1 width=5617) (actual time=3.288..7.936 rows=10
loops=25487)
               Index Cond: (p1.geom && p2.geom)
 Total runtime: 203766.439 ms
(6 rows)


test3=# select postgis_full_version();
                                postgis_full_version
-------------------------------------------------------------------------------------
 POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS
(1 row)