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

Started by Dave Blasbyover 24 years ago4 messages
#1Dave Blasby
dblasby@refractions.net

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);

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Dave Blasby (#1)
Re: Rtree on custom data types; type conversion stops index use.

Dave Blasby <dblasby@refractions.net> writes:

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.

First question: what Postgres version?

Next question (if PG >= 7.0): did you mark your type conversion routine
as cachable?

regards, tom lane

#3Dave Blasby
dblasby@refractions.net
In reply to: Dave Blasby (#1)
Re: Rtree on custom data types; type conversion stops index use.

I'm using 7.1.1, and your suggestion WORKED!

Thanks for your prompt reply!

Refractions Research will be releasing (open source) "postGIS" very soon
(as soon as its in a releasable state).
It will contain GIS data types (box3d, multi-point3d, multi-polyline3d,
multi-complex-polygon3d) and GIS operations.

Tom Lane wrote:
...

Show quoted text

First question: what Postgres version?

Next question (if PG >= 7.0): did you mark your type conversion routine
as cachable?

#4Thomas Lockhart
lockhart@alumni.caltech.edu
In reply to: Dave Blasby (#1)
Re: Rtree on custom data types; type conversion stops index use.

Refractions Research will be releasing (open source) "postGIS" very soon
(as soon as its in a releasable state).
It will contain GIS data types (box3d, multi-point3d, multi-polyline3d,
multi-complex-polygon3d) and GIS operations.

Cool!

- Thomas