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 0F997CADC5 for ; Mon, 14 Jul 2003 00:01:24 +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 29086-02 for ; Mon, 14 Jul 2003 00:01:41 +0000 (GMT) Received: from fuji.krosing.net (unknown [194.204.44.118]) by svr1.postgresql.org (Postfix) with ESMTP id 3AE34CADB8 for ; Sun, 13 Jul 2003 21:01:19 -0300 (ADT) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h6DNwUXi019163; Mon, 14 Jul 2003 02:58:30 +0300 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h6DNwS2l019161; Mon, 14 Jul 2003 02:58:28 +0300 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: Improving a simple query? From: Hannu Krosing To: Steve Wampler Cc: pgsql-performance@postgresql.org In-Reply-To: <20030713204610.GA18123@weaver.tuc.noao.edu> References: <1058119515.23012.114.camel@weaver.tuc.noao.edu> <49293.192.168.1.32.1058123357.squirrel@mainbox.archonet.com> <20030713204610.GA18123@weaver.tuc.noao.edu> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1058140707.19113.0.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 14 Jul 2003 02:58:28 +0300 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/207 X-Sequence-Number: 2500 Steve Wampler kirjutas P, 13.07.2003 kell 23:46: > 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? There may be some subtle differences, but most likely the 'join' form wis like this: select at.* from attributes_table at, attributes a where at.id = a.id and a.name='obsid' and a.value='oid00066' -------------- Hannu