[postgis-users] Strange result about spatial indexes?

[Paul Ramsey]

Wow, that's an impressive and simple demonstration, and frankly it's
hard to explain without a white board. It looks like your call to
st_expand is moving the index evaluation from one that pulls
geometries every time to one that has at least one and maybe two box2d
objects.  The net effect being a lot fewer disk accesses (as the boxes
are much much smaller than the geometries).

If you could share your table with me, I'd be interested in profiling
this workload to confirm my hypothesis.

Not much we can do about it in the near term. I have a few ideas but
nothing earth-shattering.

P.

On Thu, Aug 13, 2009 at 9:06 AM, Jose Carlos<jomarlla at cgf.upv.es> wrote:
> 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)
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>