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 4A5FDCB625 for ; Mon, 14 Jul 2003 02:30:59 +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 36965-02 for ; Mon, 14 Jul 2003 02:31:09 +0000 (GMT) Received: from hub.org (hub.org [64.117.225.220]) by svr1.postgresql.org (Postfix) with ESMTP id 7F3E6CBA91 for ; Sun, 13 Jul 2003 23:30:49 -0300 (ADT) Received: from morpheus.pgsql.com (u231n71.eastlink.ca [24.222.231.71]) by hub.org (Postfix) with ESMTP id B9EFE5B97BB; Sun, 13 Jul 2003 23:31:08 -0300 (ADT) Message-Id: <5.2.1.1.0.20030713232822.00a076c0@mail.hub.org> X-Sender: chris@pgsql.com@mail.pgsql.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Sun, 13 Jul 2003 23:31:01 -0300 To: Steve Wampler , pgsql-performance@postgresql.org From: Chris Bowlby Subject: Re: Improving a simple query? In-Reply-To: <20030713204610.GA18123@weaver.tuc.noao.edu> References: <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/209 X-Sequence-Number: 2502 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