pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres ignoring RTree for geometric operators
7+ messages / 4 participants
[nested] [flat]
* Postgres ignoring RTree for geometric operators
@ 2000-12-29 08:43 Gilles Bernard <gbernard@matra-ms2i.fr>
0 siblings, 2 replies; 7+ messages in thread
From: Gilles Bernard @ 2000-12-29 08:43 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2000-12-30 12:09 Hannu Krosing <hannu@tm.ee>
parent: Gilles Bernard <gbernard@matra-ms2i.fr>
1 sibling, 0 replies; 7+ messages in thread
From: Hannu Krosing @ 2000-12-30 12:09 UTC (permalink / raw)
To: Gilles Bernard <gbernard@matra-ms2i.fr>; +Cc: pgsql-docs
> Gilles Bernard wrote:
>
> 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');
Here you should probably do "vacuum analyze";
or just create the index _after_ inserting some amount of data tho have
the initial statistics favour index scan
------------
Hannu
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2000-12-31 22:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gilles Bernard <gbernard@matra-ms2i.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2000-12-31 22:42 UTC (permalink / raw)
To: Gilles Bernard <gbernard@matra-ms2i.fr>; +Cc: pgsql-docs
"Gilles Bernard" <gbernard@matra-ms2i.fr> writes:
>> 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)
> It seems that Postgres doesn't use the RTree index at all since it performs=
> a sequential scan on the whole table.
Try it with the clause the other way round:
... where ( forme && '((20000,20000),(30000,25000))' )
Var on the left is the required normal form for indexscan restriction
clauses. Postgres should be able to figure out that it can flip your
clause as given into that form ... but for some reason, && is not marked
as commutative in the 7.0 system catalogs, so it won't do it for you.
I have fixed that for 7.1. If you really want to write the var on the
right side right now, you could patch your system catalogs for yourself:
UPDATE pg_operator SET oprcom = oid WHERE oprname = '&&'
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2001-01-01 13:53 Ralf Mattes <rm@mh-freiburg.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Ralf Mattes @ 2001-01-01 13:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gilles Bernard <gbernard@matra-ms2i.fr>; pgsql-docs
On Sun, Dec 31, 2000 at 05:42:34PM -0500, Tom Lane wrote:
[..]
> Var on the left is the required normal form for indexscan restriction
> clauses. Postgres should be able to figure out that it can flip your
> clause as given into that form ... but for some reason, && is not marked
> as commutative in the 7.0 system catalogs, so it won't do it for you.
>
> I have fixed that for 7.1. If you really want to write the var on the
> right side right now, you could patch your system catalogs for yourself:
>
> UPDATE pg_operator SET oprcom = oid WHERE oprname = '&&'
>
Still, the remarkl about running 'vacuum' after the creation
of an index seems valid. I was bitten by this just last week--
somehow it seems counterintuitive to have to vacuum a table only
to tell the system that an index exists. This should be the job
of 'create index' or am i wrong?
Ralf Mattes
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2001-01-01 18:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ralf Mattes <rm@mh-freiburg.de>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2001-01-01 18:50 UTC (permalink / raw)
To: Ralf Mattes <rm@mh-freiburg.de>; +Cc: Gilles Bernard <gbernard@matra-ms2i.fr>; pgsql-docs
Ralf Mattes <rm@mh-freiburg.de> writes:
> Still, the remarkl about running 'vacuum' after the creation
> of an index seems valid. I was bitten by this just last week--
> somehow it seems counterintuitive to have to vacuum a table only
> to tell the system that an index exists. This should be the job
> of 'create index' or am i wrong?
The system knows perfectly well that the index exists. The issue
is whether the planner will conclude that the index is worth using
for a particular query, in the absence of complete statistical
information. If you've never done a 'vacuum analyze' on the table
then the planner is flying blind about what to do (and no, it does
not matter whether the index exists at the time the vacuum is done).
There are some subtle interactions between the default estimates that
are made for various parameters. The current behavior clearly needs
work, but I'm hesitant to "fix" it by just lowering the default
selectivity estimate (or some such) without careful study.
I'm hoping to have some time to spend on that issue for 7.2 ...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2001-01-03 15:09 Gilles Bernard <gbernard@matra-ms2i.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Gilles Bernard @ 2001-01-03 15:09 UTC (permalink / raw)
To: Ralf Mattes <rm@mh-freiburg.de>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-docs
I tryed all you suggested me : (forme && '((...))'), drop the index and
recreate it and do a vacuum analyze and it
still do a sequential scan on the whole table.
> Ralf Mattes <rm@mh-freiburg.de> writes:
> > Still, the remarkl about running 'vacuum' after the creation
> > of an index seems valid. I was bitten by this just last week--
> > somehow it seems counterintuitive to have to vacuum a table only
> > to tell the system that an index exists. This should be the job
> > of 'create index' or am i wrong?
>
> The system knows perfectly well that the index exists. The issue
> is whether the planner will conclude that the index is worth using
> for a particular query, in the absence of complete statistical
> information. If you've never done a 'vacuum analyze' on the table
> then the planner is flying blind about what to do (and no, it does
> not matter whether the index exists at the time the vacuum is done).
>
> There are some subtle interactions between the default estimates that
> are made for various parameters. The current behavior clearly needs
> work, but I'm hesitant to "fix" it by just lowering the default
> selectivity estimate (or some such) without careful study.
>
> I'm hoping to have some time to spend on that issue for 7.2 ...
>
> regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Postgres ignoring RTree for geometric operators
@ 2001-01-03 16:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-01-03 16:24 UTC (permalink / raw)
To: Gilles Bernard <gbernard@matra-ms2i.fr>; +Cc: Ralf Mattes <rm@mh-freiburg.de>; pgsql-docs
"Gilles Bernard" <gbernard@matra-ms2i.fr> writes:
> I tryed all you suggested me : (forme && '((...))'), drop the index and
> recreate it and do a vacuum analyze and it
> still do a sequential scan on the whole table.
Hm. You might try reducing the selectivity estimates returned by
areasel() and friends in src/backend/utils/adt/geo_selfuncs.c.
I thought they were small enough already, but perhaps not.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-01-03 16:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-29 08:43 Postgres ignoring RTree for geometric operators Gilles Bernard <gbernard@matra-ms2i.fr>
2000-12-30 12:09 ` Hannu Krosing <hannu@tm.ee>
2000-12-31 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-01 13:53 ` Ralf Mattes <rm@mh-freiburg.de>
2001-01-01 18:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-03 15:09 ` Gilles Bernard <gbernard@matra-ms2i.fr>
2001-01-03 16:24 ` Tom Lane <tgl@sss.pgh.pa.us>
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