Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 73A2F6500B4 for ; Wed, 30 Jul 2008 19:12:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 90766-06 for ; Wed, 30 Jul 2008 19:12:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 0E90D64FFE4 for ; Wed, 30 Jul 2008 19:12:12 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1KOJtz-0006Hn-27 for pgsql-performance@postgresql.org; Wed, 30 Jul 2008 22:12:11 +0000 Received: from apn-77-113-78-203.gprs.plus.pl ([77.113.78.203]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 30 Jul 2008 22:12:11 +0000 Received: from public by apn-77-113-78-203.gprs.plus.pl with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 30 Jul 2008 22:12:11 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-performance@postgresql.org From: Miernik Subject: what is less resource-intensive, WHERE id IN or INNER JOIN? Date: Thu, 31 Jul 2008 00:11:58 +0200 Lines: 16 Message-ID: <20080730221158.5323.1.NOFFLE@turbacz.local> Reply-To: Miernik X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: apn-77-113-78-203.gprs.plus.pl X-Archive: encrypt=none User-Agent: tin/1.9.3-20080506 ("Dalintober") (UNIX) (Linux/2.6.25-2-686 (i686)) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/259 X-Sequence-Number: 30859 AFAIK, provided bar is UNIQUE in table2 (e.g. is a PRIMARY KEY) the two queries will give the same result: SELECT foo, id FROM table1 WHERE id IN (SELECT id FROM table2); SELECT foo, id FROM table1 INNER JOIN table2 USING (id); Given table1 has about 100k rows, and table2 about 100 rows, which one should be faster, less resource intensive, use less RAM, disk access, etc? Are there any other even better ways to acomlish the same query? Using 8.3.3 on a 48 MB RAM Xen. -- Miernik http://miernik.name/