Rtree on custom data types; type conversion stops index use.

From: Dave Blasby <dblasby(at)refractions(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Rtree on custom data types; type conversion stops index use.
Date: 2001-05-11 20:00:50
Message-ID: 3AFC44F2.E71B2E64@refractions.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I've create a few types, including BOX3D (a simple bounding volume) and
AGG_POINTS3D (a list of points with a BOX3D bounding volume).

I've managed to get an rtree index on both the BOX3D type and
AGG_POINTS3D.
The agg_points3d index cheats by using the bounding volume inside the
AGG_POINTS3D type.

I've "SET ENABLE_SEQSCAN=OFF;" so it usually uses the rtree index when I
do things like:

select * from box_table where the_box && <hard coded box3d>;

for example;
select * from test_box where the_box &&
'BOX3D (
[4273.95215,12385.8281,0.0],[4340.80566,12459.7949,0.0])'::BOX3D;

OR

select * from test_points3d where the_pts && <hard coded agg_points3d
object>;

for example;
select * from test_pts where the_pts &&

'POINTS3D([10077.4414,14361.6172,1.0],[12370.2773,14595.5791,1.0],[13259.3379,11554.0596,1.0],[10872.915,10477.8301,1.0])'::AGG_POINTS3D;

I'm sure it using the rtree index because 'explain' says it does and its
about 10* faster than a sequence scan.

So far, so good. I'm happy.

Now I want to be able to do an index scan into the AGG_POINTS3D table
against a BOX3D. This is essentually what the rtree index is doing
anyways.

I defined a function agg_points3d(BOX3D) that converts the BOX3D into
an AGG_POINTS3D.

The query:

select loc from test_pts where the_pts &&

'BOX3D([10077.4414,10477.8301,1.0],[13259.3379,14595.5791,1.0])'::BOX3D;

gives the correct results. Postgres automatically uses the
agg_points3d() function to convert the BOX3D into an AGG_POINTS3D.
Unfortunately, it doesn't use the index scan anymore; it does a sequence
scan.

I tried the following queries as well;

select * from test_points3d where the_pts &&
agg_points3d(
'BOX3D([10077.4414,10477.8301,1.0],[13259.3379,14595.5791,1.0])'::BOX3D
);

[Explicitly doing the above]

select * from test_points3d where the_pts &&
(agg_points3d(
'BOX3D([10077.4414,10477.8301,1.0],[13259.3379,14595.5791,1.0])'::BOX3D
))::AGG_POINTS3D;

[Ensuring postgres knows that the 2nd argument to && is an AGG_POINTS3D]

My question is why isnt it doing an index scan? And how do I get it to
use the index? The above 3 queries are really queries like:

select * from test_points3d where the_pts && <AGG_POINTS3D>;

which does use an index scan?

Thanks,

dave
ps. The tables are defined as:
create table test_points3d (loc varchar(100), the_pts AGG_POINTS3D) ;
create table test_box (loc varchar(100), the_box BOX3D);
Both tables have about 200,000 random rows in them for testing.

I create the indexes with:

create index rt_test_box on test_box using rtree (the_box
rt_box3d_ops);
create index rt_test_points on test_points3d using rtree (the_pts
rt_points3d_ops);

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-05-11 20:19:50 Re: Rtree on custom data types; type conversion stops index use.
Previous Message Tom Lane 2001-05-11 19:59:40 Re: Problem with a rule on upgrade to v7.1.1