RTREE on points

From: "Julian Scarfe" <julian(at)avbrief(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: RTREE on points
Date: 2001-04-15 10:43:01
Message-ID: 9bbtqv$688$1@news.tht.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephen Jackson 2001-04-15 16:10:29 Re: [SQL] g++ not working for postgresql extension languages?
Previous Message Poet/Joshua Drake 2001-04-15 06:01:47 Re: Same question about PostgreSql