Обсуждение: Postgres ignoring RTree for geometric operators

> 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

"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

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

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

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

"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