[postgis-users] Indexes used in 1.3.3 but not in 1.5.3

[Paragon Corporation]

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-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Frederic
Junod
Sent: Thursday, October 20, 2011 11:32 AM
To: postgis-users at 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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users