I've tried to increase the cost of _ST_Buffer and _ST_Overlaps function but without success. Then, I've tried to lower the cost of ST_Buffer to 1 and the index is used.
Now I'll try to see if it works for others queries (stored in views in my database). Many thanks for your hints Regina, it helps a lot ! fredj On Thu, Oct 20, 2011 at 10:42 PM, Paragon Corporation <l...@pcorp.us> wrote: > Fred, > Try increasing the cost of _st_overlaps and _st_buffer so it doesn't know > using these is more costly than the index or the index costs we have may be > messed up in some way. > > There is a report that we might have these set too low. I haven't done > enough analysis to know wha the right costing would be. > http://trac.osgeo.org/postgis/ticket/1248 > > --- > > CREATE OR REPLACE FUNCTION _st_covers(geometry, geometry) > RETURNS boolean AS > '$libdir/postgis-1.5', 'covers' > LANGUAGE c IMMUTABLE STRICT > COST 800; > > CREATE OR REPLACE FUNCTION _st_buffer(geometry, double precision, cstring) > RETURNS geometry AS > '$libdir/postgis-1.5', 'buffer' > LANGUAGE c IMMUTABLE STRICT > COST 1000; > > You might want to fiddle with the numbers a bit. > > Newer versions of PostgreSQL might be doing something different with the > costs than prior or weighting gist index costs higher than before. I > believe the cost settings came out in 8.3 so your old should be using > costing metrics as well so not sure why it would switch between now. > > Hope that helps, > Regina > http://www.postgis.us > > -----Original Message----- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Frederic > Junod > Sent: Thursday, October 20, 2011 11:32 AM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3 > > 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 > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Frédéric Junod Camptocamp SA _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
