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 81671CB5E7 for ; Sun, 13 Jul 2003 19:08:57 +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 56523-06 for ; Sun, 13 Jul 2003 19:09:18 +0000 (GMT) Received: from anchor-post-39.mail.demon.net (anchor-post-39.mail.demon.net [194.217.242.80]) by svr1.postgresql.org (Postfix) with ESMTP id 9319DCB539 for ; Sun, 13 Jul 2003 16:08:55 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-39.mail.demon.net with esmtp (Exim 3.36 #2) id 19bmDm-0001GJ-0U; Sun, 13 Jul 2003 20:09:19 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id CD32017A24; Sun, 13 Jul 2003 20:09:17 +0100 (BST) Received: from mainbox.archonet.com (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with SMTP id 283621757D; Sun, 13 Jul 2003 20:09:17 +0100 (BST) Received: from 192.168.1.32 (SquirrelMail authenticated user dev) by mainbox.archonet.com with HTTP; Sun, 13 Jul 2003 20:09:17 +0100 (BST) Message-ID: <49293.192.168.1.32.1058123357.squirrel@mainbox.archonet.com> In-Reply-To: <1058119515.23012.114.camel@weaver.tuc.noao.edu> References: <1058119515.23012.114.camel@weaver.tuc.noao.edu> Date: Sun, 13 Jul 2003 20:09:17 +0100 (BST) Subject: Re: Improving a simple query? From: "Richard Huxton" To: swampler@noao.edu Cc: "Postgres-performance" User-Agent: SquirrelMail/1.4.0 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 X-Priority: 3 Importance: Normal X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/198 X-Sequence-Number: 2491 > 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? - Richard