Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBU2atL62894 for ; Fri, 29 Dec 2000 21:36:55 -0500 (EST) (envelope-from pgsql-docs-owner@postgresql.org) Received: from fwm1.matra-ms2i.fr (fwm1.matra-ms2i.fr [195.46.202.129]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBT8hrL81215 for ; Fri, 29 Dec 2000 03:43:53 -0500 (EST) (envelope-from gbernard@matra-ms2i.fr) Received: by fwm1.matra-ms2i.fr; id KAA01221; Fri, 29 Dec 2000 10:46:07 +0100 (CET) Message-Id: <200012290946.KAA01221@fwm1.matra-ms2i.fr> From: "Gilles Bernard" To: Subject: Postgres ignoring RTree for geometric operators Date: Fri, 29 Dec 2000 09:43:31 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C0717B.CE11E810" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Archive-Number: 200012/74 X-Sequence-Number: 121 This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C0717B.CE11E810 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'm trying to figure out how Postgres (7.0.3 on a DEC alpha OSF1 4.0= F) deals with geometric types and operators. So I've create a table like this :=20 >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 :=20 >create index geo_is on geo using RTREE (forme); The table is populated with 400,000 lines inserted like this :=20 >insert into geo values (1, '((51387.07,17572.56),(51440.62,17626.11))',308= 63.37,30916.92,'Donnee 1'); >insert into geo values (2, '((94763.02,17172.77),(94785.77,17195.51))',702= 23.09,70245.83,'Donnee 2'); >insert into geo values (3, '((49476.61,12469.86),(49515.67,12508.93))',838= 9.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 :=20 >bernardg=3D# explain select count(*) from geo where ('((20000,20000),(3000= 0,25000))' && forme ) and (hmin>20000) and (hmax<25000); >NOTICE: QUERY PLAN: > >Aggregate (cost=3D13595.20..13595.20 rows=3D1 width=3D4) > -> Seq Scan on geo (cost=3D0.00..13592.98 rows=3D889 width=3D4) > >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 ------=_NextPart_000_0010_01C0717B.CE11E810 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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

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

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

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

The table is populated with 400,000 li= nes=20 inserted like this :

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

I ask Postgres to explain the query :

>bernardg=3D# explain select count(*) f= rom geo=20 where ('((20000,20000),(30000,25000))' && forme ) and (hmin>2000= 0)=20 and (hmax<25000);
>NOTICE: QUERY=20 PLAN:
>
>Aggregate (cost=3D13595.20..13595.20 rows=3D1=20 width=3D4)
> -> Seq Scan on geo (cost=3D0.00..13= 592.98=20 rows=3D889 width=3D4)
>
>EXPLAIN

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

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

Thanks in advance

Gilles Bernard

------=_NextPart_000_0010_01C0717B.CE11E810--