From: Julian Scarfe <julian@avbrief.com>
To: pgsql-sql@postgresql.org
Subject: RTREE on points
Date: Sun, 15 Apr 2001 11:43:01 +0100
Message-ID: <9bbtqv$688$1@news.tht.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: julian@avbrief.com
Subject: Re: RTREE on points
In-Reply-To: <9bbtqv$688$1@news.tht.net>
References: <9bbtqv$688$1@news.tht.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox