Hello,
With postgis 1.3.3 on postgresql 8.3.14, the following select uses the indexes:
EXPLAIN SELECT 1 FROM grid_pk25, grid_geocover WHERE
st_overlaps(st_buffer(grid_geocover.the_geom, -1),
grid_pk25.the_geom);
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..128.61 rows=1 width=0)
Join Filter: _st_overlaps(st_buffer(grid_geocover.the_geom,
(-1)::double precision), grid_pk25.the_geom)
-> Seq Scan on grid_geocover (cost=0.00..8.22 rows=222 width=813)
-> Index Scan using grid_pk25_the_geom_1150804304770 on grid_pk25
(cost=0.00..0.52 rows=1 width=1217)
Index Cond: (st_buffer(grid_geocover.the_geom, (-1)::double
precision) && grid_pk25.the_geom)
Filter: (st_buffer(grid_geocover.the_geom, (-1)::double
precision) && grid_pk25.the_geom)
The same query, with postgis 1.5.3 on postgresql 9.0.4 do not uses the indexes:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..29606.88 rows=19240 width=0)
Join Filter: st_overlaps(st_buffer(grid_geocover.the_geom,
(-1)::double precision), grid_pk25.the_geom)
-> Seq Scan on grid_pk25 (cost=0.00..15.60 rows=260 width=1217)
-> Materialize (cost=0.00..10.33 rows=222 width=813)
-> Seq Scan on grid_geocover (cost=0.00..9.22 rows=222 width=813)
(5 rows)
The database was imported with postgis_restore.pl. I've also tried to
reindex the database.
Note that on both instances, if the st_buffer is removed the indexes are used.
What's happening ?
Regards,
fredj
--
Frédéric Junod
Camptocamp SA
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
