Am I missing the point (no pun intended ;-) of RTREE indices?
I was expecting a "point_ops" opclass or similar...
[7.1 on RedHat 6.2]
SELECT am.amname AS acc_name,
opc.opcname AS ops_name,
COUNT(*)
FROM pg_am am, pg_amop amop,
pg_opclass opc
WHERE amop.amopid = am.oid AND
amop.amopclaid = opc.oid AND
am.amname = 'rtree'
GROUP BY am.amname, opc.opcname
ORDER BY acc_name, ops_name;
acc_name | ops_name | count
----------+------------+-------
rtree | bigbox_ops | 8
rtree | box_ops | 8
rtree | poly_ops | 8
(3 rows)
Surely the most natural application of an RTREE is to index points, as well
as boxes and polygons. E.g.
CREATE TABLE "nodes" (
"node" point,
"node_name" character varying(30)
);
CREATE
INSERT INTO nodes VALUES ('(1,1)', 'a');
INSERT 207372 1
INSERT INTO nodes VALUES ('(1,2)', 'b');
INSERT 207373 1
INSERT INTO nodes VALUES ('(3,2)', 'c');
INSERT 207374 1
INSERT INTO nodes VALUES ('(5,4)', 'd');
INSERT 207375 1
INSERT INTO nodes VALUES ('(7,8)', 'e');
INSERT 207376 1
INSERT INTO nodes VALUES ('(11,10)', 'f');
INSERT 207377 1
INSERT INTO nodes VALUES ('(101,11)', 'g');
INSERT 207378 1
explain select * from nodes where node @ '((1,1),(3,3))'::box;
NOTICE: QUERY PLAN:
Seq Scan on nodes (cost=0.00..22.50 rows=500 width=28)
So create an RTREE index to help...but predictably:
CREATE INDEX test_rtree ON nodes USING RTREE (node);
ERROR: DefineIndex: type point has no default operator class
I can do something like:
CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node));
CREATE
but then:
explain select * from nodes where node @ '((1,1),(3,3))'::box;
NOTICE: QUERY PLAN:
Seq Scan on nodes (cost=0.00..1.09 rows=4 width=28)
and even:
explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box;
NOTICE: QUERY PLAN:
Seq Scan on nodes (cost=0.00..1.10 rows=1 width=28)
Thanks for any help
Julian Scarfe
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
