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