Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-docs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group