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

[Frederic Junod]

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 <lr at 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-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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



-- 
Frédéric Junod
Camptocamp SA