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

Fri, 11 May 2001 13:00:50 -0700
Dave Blasby <dblasby@refractions.net>
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

Fri, 11 May 2001 16:19:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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:
...
> First question: what Postgres version?
> 
> Next question (if PG >= 7.0): did you mark your type conversion routine
> as cachable?

Fri, 11 May 2001 13:50:43 -0700
Dave Blasby <dblasby@refractions.net>
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

Sat, 12 May 2001 04:09:07 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Rtree on custom data types; type conversion stops index use.