RTREE on points

Поиск
Список
Период
Сортировка
От Julian Scarfe
Тема RTREE on points
Дата
Msg-id 9bbtqv$688$1@news.tht.net
обсуждение исходный текст
Список 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_opclassopc    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 |     8rtree    | box_ops    |     8rtree    | 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




В списке pgsql-sql по дате отправления:

Предыдущее
От: Maurizio Ortolan
Дата:
Сообщение: How to simulate MEMO data type?Thanks!
Следующее
От: "Mitch Vincent"
Дата:
Сообщение: Re: How to simulate MEMO data type?Thanks!