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 по дате отправления: