X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CB4CF47653B for ; Wed, 14 May 2003 22:25:53 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 91984-07 for ; Wed, 14 May 2003 22:25:52 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 22ABA476539 for ; Wed, 14 May 2003 22:25:52 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4F2PuU6004922; Wed, 14 May 2003 22:25:56 -0400 (EDT) To: "T. Alex Beamish" Cc: "Postgresql-Performance" Subject: Re: INNER JOIN vs WHERE In-reply-to: <20030515014648.NRXV26559.tomts19-srv.bellnexxia.net@p433> References: <20030515014648.NRXV26559.tomts19-srv.bellnexxia.net@p433> Comments: In-reply-to "T. Alex Beamish" message dated "Wed, 14 May 2003 21:45:04 -0400" Date: Wed, 14 May 2003 22:25:55 -0400 Message-ID: <4921.1052965555@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/175 X-Sequence-Number: 1954 "T. Alex Beamish" writes: > Is there any rule of thumb about how much more efficient an INNER JOIN > is compare to a regular WHERE statement? Ideally there is no difference. If there are only two tables involved, there definitely is no difference. If there is a difference, it's because there are more than two tables, and the JOIN syntax forced a particular join order. Read http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=explicit-joins.html Typically I'd expect JOIN syntax to be a loss because the odds are good that you're forcing an inefficient join order. It could be a win only if the planner chooses a poor join order when given a free hand, or if you have so many tables that you need to suppress the planner's search for a good join order. > I have not tried to do an EXPLAIN ANALYZE yet but I will try that. If you have not bothered to do any EXPLAINs yet then you are really wasting people's time on this list. regards, tom lane