X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 4296ECB5B7 for ; Mon, 14 Jul 2003 01:23:31 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 75989-04 for ; Mon, 14 Jul 2003 01:23:49 +0000 (GMT) Received: from celery.tssi.com (celery.tssi.com [198.147.197.6]) by svr1.postgresql.org (Postfix) with SMTP id 5ACD2CB597 for ; Sun, 13 Jul 2003 22:23:28 -0300 (ADT) Received: (qmail 18921 invoked by uid 1000); 14 Jul 2003 01:23:47 -0000 Message-ID: <20030714012347.18920.qmail@celery.tssi.com> From: nolan@celery.tssi.com Subject: Re: Improving a simple query? To: swampler@noao.edu (Steve Wampler) Date: Sun, 13 Jul 2003 20:23:47 -0500 (CDT) Cc: pgsql-performance@postgresql.org In-Reply-To: <20030713204610.GA18123@weaver.tuc.noao.edu> from "Steve Wampler" at Jul 13, 2003 01:46:10 PM X-Mailer: ELM [version 2.5 PL3] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/208 X-Sequence-Number: 2501 > > 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? This may be a question for SQL theoretists, but I don't think I've ever run across a query with a 'where in' clause that couldn't be written as a join. I think linguistically 'where in' may even be a special case of 'join'. Yet another question for the theoretists: Would it be possible to optimize a 'where in' query by rewriting it as a join? -- Mike Nolan