Return-Path: owner-postman Received: from localhost (localhost [127.0.0.1]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with SMTP id OAA20582 for postgres-dist; Thu, 10 Mar 1994 14:33:27 -0800 Resent-From: POSTGRES mailing list Resent-Message-Id: <199403102233.OAA20582@nobozo.CS.Berkeley.EDU> X-Authentication-Warning: nobozo.CS.Berkeley.EDU: Host localhost didn't use HELO protocol Sender: owner-postman@postgres.Berkeley.EDU X-Return-Path: owner-postman Received: from kaukau.comp.vuw.ac.nz (kaukau.comp.vuw.ac.nz [130.195.5.20]) by nobozo.CS.Berkeley.EDU (8.6.4/8.6.3) with ESMTP id OAA20573 for ; Thu, 10 Mar 1994 14:33:17 -0800 Received: from downstage.comp.vuw.ac.nz (downstage.comp.vuw.ac.nz [130.195.6.10]) by kaukau.comp.vuw.ac.nz (8.6.4/8.6.4) with ESMTP id LAA04640; Fri, 11 Mar 1994 11:32:38 +1300 From: Aaron Roydhouse Received: from downstage.comp.vuw.ac.nz (aaron@localhost) by downstage.comp.vuw.ac.nz (8.6.4/8.6.4) with ESMTP id LAA13131; Fri, 11 Mar 1994 11:32:54 +1300 Message-Id: <199403102232.LAA13131@downstage.comp.vuw.ac.nz> To: postgres@postgres.Berkeley.EDU cc: aoki@postgres.Berkeley.EDU Subject: Re: Hoping the optimiser will consider my index Date: Fri, 11 Mar 1994 11:32:52 +1300 Resent-To: postgres-dist@postgres.Berkeley.EDU Resent-Date: Thu, 10 Mar 94 14:33:27 -0800 Resent-XMts: smtp From: aoki@postgres.Berkeley.EDU (Paul M. Aoki) > > Aaron Roydhouse writes: > > retrieve (r.bounds) from r in region where "(130,-45,135,-50)"::box && r.bounds > > this is going to sound stupid, but what happens when you reverse > the left/right arguments of &&? Du-oh, I guess I should have thought of that. This does the trick! Given an index such as define index reg_bounds on region using rtree (bounds box_ops) Then this query retrieve (r.bounds) from r in region where "(130,-45,135,-50)"::box && r.bounds will not use the index, but this query retrieve (r.bounds) from r in region where r.bounds && "(130,-45,135,-50)"::box will (I've attached the resulting plans below). Paul, thanks for your help, perhaps the team should put out an informal `Rules of Engagement' for the postgres optimiser :-) Is there any other gotchas or query forming advice available? I remember multijoins are better chained that centralised (a = b, b = c is better than a = b, a = b) Aaron. ---------------------------------------- define index reg_bounds on region using rtree (bounds box_ops) retrieve (r.bounds) from r in region where "(130,-45,135,-50)"::box && r.bounds Plan is : #S(seqscan :cost 346284 :size 0 :width 32 :fragment 0 :parallel 1 :state nil :qptargetlist ((#S(resdom :resno 1 :restype 603 :rescomplex nil :reslen 32 :resname "bounds" :reskey 0 :reskeyop 0 :resjunk 0)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :qpqual ((#S(oper :opno 500 :opid 125 :oprelationlevel nil :opresulttype 16)#S(const :consttype 603 :constlen 32 :constisnull nil :constvalue 32 { 64 96 -32 0 0 0 0 0 -64 70 -128 0 0 0 0 0 64 96 64 0 0 0 0 0 -64 73 0 0 0 0 0 0 } :constbyval nil)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :lefttree nil :righttree nil :scanrelid 1) ProcessQuery() at Thu Mar 10 21:52:28 1994 retrieve (r.bounds) from r in region where r.bounds && "(130,-45,135,-50)"::box Plan is : #S(indexscan :cost 1183.61 :size 1797 :width 32 :fragment 0 :parallel 1 :state nil :qptargetlist ((#S(resdom :resno 1 :restype 603 :rescomplex nil :reslen 32 :resname "bounds" :reskey 0 :reskeyop 0 :resjunk 0)#S(var :varno 1 :varattno 8 :vartype 603 :varid (1 8 )))) :qpqual nil :lefttree nil :righttree nil :scanrelid 1 :indxid (546368 ) :indxqual (((#S(oper :opno 500 :opid 125 :oprelationlevel nil :opresulttype 16)#S(var :varno 1 :varattno 1 :vartype 603 :varid (1 8 ))#S(const :consttype 603 :constlen 32 :constisnull nil :constvalue 32 { 64 96 -32 0 0 0 0 0 -64 70 -128 0 0 0 0 0 64 96 64 0 0 0 0 0 -64 73 0 0 0 0 0 0 } :constbyval nil)))))