From: Gilles Bernard <gbernard@matra-ms2i.fr>
To: pgsql-docs@postgresql.org
Subject: Postgres ignoring RTree for geometric operators
Date: Fri, 29 Dec 2000 09:43:31 +0100
Message-ID: <200012290946.KAA01221@fwm1.matra-ms2i.fr> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: gbernard@matra-ms2i.fr
Subject: Re: Postgres ignoring RTree for geometric operators
In-Reply-To: <200012290946.KAA01221@fwm1.matra-ms2i.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox