Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3FGoEu80476 for ; Sun, 15 Apr 2001 12:50:14 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3FAtMu06299 for ; Sun, 15 Apr 2001 06:55:33 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f3FAeX106412 for pgsql-sql@postgresql.org; Sun, 15 Apr 2001 06:40:33 -0400 (EDT) (envelope-from news) From: "Julian Scarfe" X-Newsgroups: comp.databases.postgresql.sql Subject: RTREE on points Date: Sun, 15 Apr 2001 11:43:01 +0100 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 79 Message-ID: <9bbtqv$688$1@news.tht.net> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200104/236 X-Sequence-Number: 2586 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