[postgis-users] spatial index use

[strk]

On Tue, Jun 01, 2004 at 11:10:25AM +0200, Laurent PIERRE wrote:
> 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 ?

SET ENABLE_SEQSCAN=off
SET ENABLE_INDEXSCAN=on

But do you really get 2721579 rows overlapping your box ??
How many rows does the table contain ?
Check if an index scan is faster and please let me know.
Also, what postgresql and postgis versions are you using ?

--strk;

> 
> Laurent
> 
> 
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users