[postgis-users] spatial index use

[Laurent PIERRE]

Hi,

I have problems with the query planner :

using table

                                         Table "public.isis_entreprise"
       Column        |          Type          
|                            Modifiers                           
---------------------+------------------------+-----------------------------------------------------------------
 id                  | integer                | not null default 
nextval('public.isis_entreprise_id_seq'::text)
 
 coord               | geometry               |

Indexes:
    "isis_entreprise_pkey" primary key, btree (id)
    "i_entreprise_coord" gist (coord)
Check constraints:
    "$1" CHECK (srid(coord) = 102582)
    "$2" CHECK (geometrytype(coord) = 'POINT'::text OR coord IS NULL)


the query



select *
from
isis_adresse_geo p,
isis_adresse_geo g2
where
p.id = 2709333  and

GeometryFromText('BOX3D('
|| X(p.coord)-100000
|| ' '
|| X(p.coord)+100000
|| ','
|| Y(p.coord)-100000
|| ' '
|| Y(p.coord)+100000
||')',SRID(p.coord))
 && g2.coord


is 'explained' as

 Nested Loop  (cost=0.00..310072.33 rows=272158 width=434)
   Join Filter: (geometryfromtext(((((((((('BOX3D('::text || 
((x("outer".coord) - 100000::double precision))::text) || ' '::text) || 
((x("outer".coord) + 100000::double precision))::text) || ','::text) || 
((y("outer".coord) - 100000::double precision))::text) || ' '::text) || 
((y("outer".coord) + 100000::double precision))::text) || 
')'::text))::geometry, srid("outer".coord)) && "inner".coord)
   ->  Index Scan using adresse_geo_id on isis_adresse_geo p  
(cost=0.00..3.01 rows=1 width=217)
         Index Cond: (id = 2709333)
   ->  Seq Scan on isis_adresse_geo g2  (cost=0.00..119558.79 
rows=2721579 width=217)
(5 rows)



the planner is unable to take advantage of the spatial index ... 
leading  to very poor performances :(

Is there a solution to force the planner ?

Laurent