Postgres ignoring RTree for geometric operators

From: "Gilles Bernard" <gbernard(at)matra-ms2i(dot)fr>
To: <pgsql-docs(at)postgresql(dot)org>
Subject: Postgres ignoring RTree for geometric operators
Date: 2000-12-29 08:43:31
Message-ID: 200012290946.KAA01221@fwm1.matra-ms2i.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

Hello, I'm trying to figure out how Postgres (7.0.3 on a DEC alpha OSF1 4.0F) deals with geometric types and operators.
So I've create a table like this :

>create table geo
>(
> id int4 primary key,
> forme box,
> hmin float4,
> hmax float4,
> nom varchar(40)
>);

I've created a RTree index on the 'forme' field :

>create index geo_is on geo using RTREE (forme);

The table is populated with 400,000 lines inserted like this :

>insert into geo values (1, '((51387.07,17572.56),(51440.62,17626.11))',30863.37,30916.92,'Donnee 1');
>insert into geo values (2, '((94763.02,17172.77),(94785.77,17195.51))',70223.09,70245.83,'Donnee 2');
>insert into geo values (3, '((49476.61,12469.86),(49515.67,12508.93))',8389.54,8428.60,'Donnee 3');
> ...
>insert into geo values (399998, '((86788.54,86867.89),(86857.69,86937.04))',86144.60,86213.75,'Donnee 399998');
>insert into geo values (399999, '((94317.45,3723.26),(94413.56,3819.36))',70345.16,70441.27,'Donnee 399999');

I ask Postgres to explain the query :

>bernardg=# explain select count(*) from geo where ('((20000,20000),(30000,25000))' && forme ) and (hmin>20000) and (hmax<25000);
>NOTICE: QUERY PLAN:
>
>Aggregate (cost=13595.20..13595.20 rows=1 width=4)
> -> Seq Scan on geo (cost=0.00..13592.98 rows=889 width=4)
>
>EXPLAIN

It seems that Postgres doesn't use the RTree index at all since it performs a sequential scan on the whole table.

So is there a way to make Postgres use the RTree index ?
I'm trying to make a primary filter (only using the spatial index) I don't need an exact match. Is there a way to do this ?

Thanks in advance

Gilles Bernard

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Bruce Momjian 2000-12-29 19:00:13 Re: [HACKERS] About PQsetClientEncoding(), "SET NAMES", and "SET CLIENT_ENCODING"
Previous Message Tatsuo Ishii 2000-12-27 23:36:41 Re: About PQsetClientEncoding(),"SET NAMES",and "SET CLIENT_ENCODING"