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

[Frederic Junod]

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