Re: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3
On 20/10/11 16:31, Frederic Junod wrote: 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 ? Hi Frederic, You'll have to post the EXPLAIN ANALYZE output if you want more help with this one. Also note that just because an index is there, it may not always be used because sequential scans can be used in a lot of cases. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3
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 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
Re: [postgis-users] Indexes used in 1.3.3 but not in 1.5.3
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
[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
