Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f3H5G7u65499 for ; Tue, 17 Apr 2001 01:16:07 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3GFaSu22591 for ; Mon, 16 Apr 2001 11:36:28 -0400 (EDT) (envelope-from julian@avbrief.com) Received: from mta03-svc.ntlworld.com (mta03-svc.ntlworld.com [62.253.162.43]) by mail1.hub.org (8.11.3/8.11.1) with ESMTP id f3GFaRx22584 for ; Mon, 16 Apr 2001 11:36:27 -0400 (EDT) (envelope-from julian@avbrief.com) Received: from julian ([62.253.133.34]) by mta03-svc.ntlworld.com (InterMail vM.4.01.02.27 201-229-119-110) with SMTP id <20010416153147.PBNC283.mta03-svc.ntlworld.com@julian>; Mon, 16 Apr 2001 16:31:47 +0100 Message-ID: <008f01c0c68a$b6651c00$2285fd3e@julian> From: "Julian Scarfe" To: "Jeff Hoffmann" Cc: References: <9bbtqv$688$1@news.tht.net> <3ADAFF38.E7147C8D@propertykey.com> Subject: Re: RTREE on points Date: Mon, 16 Apr 2001 16:34:23 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200104/263 X-Sequence-Number: 2613 Julian Scarfe wrote: > > > > 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) From: "Jeff Hoffmann" > this should work, assuming you have enough points to make a difference > (in the optimizer's mind, at least). the optimizer still doesn't do a > great job of knowing when it's best to use an index, although, in your > sample, there's no way it would ever be cheaper to use an index. > there's simply not enough data there. you can test to see if an index > can be used by a query by shutting off the sequential scans (set > enable_seqscan=off) and retrying the query. essentially, this forces it > to use an index scan if at all possible. And indeed it does, thank you, Jeff: # set enable_seqscan=off; SET VARIABLE # explain select * from nodes where box(node,node) @ '((1,1),(3,3))'::box; NOTICE: QUERY PLAN: Index Scan using test_rtree on nodes (cost=0.00..2.02 rows=1 width=28) It hadn't occured to me that the index would simply not be used and I'm grateful for the pointer to the appropriate variable. Nevertheless, wouldn't... CREATE INDEX test_rtree ON nodes USING RTREE (node); (which fails) ...be a lot simpler than... CREATE INDEX test_rtree ON nodes USING RTREE (box(node,node)); (which succeeds, as above) ? The latter feels contorted and possibly inefficient. After all, I don't do...: CREATE TABLE "nodes" ( "node" point, "node_name" character varying(30) ); INSERT INTO nodes VALUES ('(1,1)', 'a'); INSERT INTO nodes VALUES ('(1,2)', 'b'); INSERT INTO nodes VALUES ('(3,2)', 'c'); INSERT INTO nodes VALUES ('(5,4)', 'd'); INSERT INTO nodes VALUES ('(7,8)', 'e'); INSERT INTO nodes VALUES ('(11,10)', 'f'); INSERT INTO nodes VALUES ('(101,11)', 'g'); CREATE INDEX test_btree ON nodes USING BTREE (textcat(node_name,node_name)); ...if I want to index by name? (even though in principle it would work) Thanks for any guidance. Julian Scarfe