X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 8A8E5CBB6D for ; Mon, 14 Jul 2003 02:33:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 47295-01 for ; Mon, 14 Jul 2003 02:33:34 +0000 (GMT) Received: from hub.org (hub.org [64.117.225.220]) by svr1.postgresql.org (Postfix) with ESMTP id 3B809CBB69 for ; Sun, 13 Jul 2003 23:33:14 -0300 (ADT) Received: from morpheus.hub.org (u231n71.eastlink.ca [24.222.231.71]) by hub.org (Postfix) with ESMTP id 7372D5B99FE; Sun, 13 Jul 2003 23:33:33 -0300 (ADT) Message-Id: <5.2.1.1.0.20030713233301.009fd160@mail.hub.org> X-Sender: excalibur@hub.org@mail.hub.org X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Sun, 13 Jul 2003 23:33:26 -0300 To: Chris Bowlby , Steve Wampler , pgsql-performance@postgresql.org From: Chris Bowlby Subject: Re: Improving a simple query? In-Reply-To: <5.2.1.1.0.20030713232822.00a076c0@mail.hub.org> References: <20030713204610.GA18123@weaver.tuc.noao.edu> <49293.192.168.1.32.1058123357.squirrel@mainbox.archonet.com> <1058119515.23012.114.camel@weaver.tuc.noao.edu> <49293.192.168.1.32.1058123357.squirrel@mainbox.archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/210 X-Sequence-Number: 2503 At 11:31 PM 7/13/03 -0300, Chris Bowlby wrote: Woops, this might not go through via the address I used :> (not subscribed with that address).. >At 01:46 PM 7/13/03 -0700, Steve Wampler wrote: > > The following left join should work if I've done my select right, you > might want to play with a left versus right to see which will give you a > better result, but this query should help: > > SELECT * FROM attributes_table att LEFT JOIN attributes at ON (at.name = > 'obsid' AND at.value = 'oid00066') WHERE att.id = at.id; > >>On Sun, Jul 13, 2003 at 08:09:17PM +0100, Richard Huxton wrote: >> > > I'm not an SQL or PostgreSQL expert. >> > > >> > > I'm getting abysmal performance on a nested query and >> > > need some help on finding ways to improve the performance: >> > [snip] >> > > select * from attributes_table where id in (select id from >> > > attributes where (name='obsid') and (value='oid00066')); >> > >> > This is the classic IN problem (much improved in 7.4 dev I believe). The >> > recommended approach is to rewrite the query as an EXISTS form if >> > possible. See the mailing list archives for plenty of examples. >> > >> > Could you not rewrite this as a simple join though? >> >>Hmmm, I don't see how. Then again, I'm pretty much the village >>idiot w.r.t. SQL... >> >>The inner select is locating a set of (2049) ids (actually from >>the same table, since 'attributes' is just a view into >>'attributes_table'). The outer select is then locating all >>records (~30-40K) that have any of those ids. Is that really >>something a JOIN could be used for? >> >>-Steve >>-- >>Steve Wampler -- swampler@noao.edu >>Quantum materiae materietur marmota monax si marmota >> monax materiam possit materiari? >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster