# [postgis-users] Strange result about spatial indexes?

Jose Carlos jomarlla at cgf.upv.es
Thu Aug 13 09:06:57 PDT 2009

```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)

```