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<[email protected]> 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 > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
