Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA51112 for ; Wed, 7 Jul 1999 11:43:33 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id LAA26202; Wed, 7 Jul 1999 11:42:38 -0400 (EDT) To: secret cc: PG-SQL , John Ridout Subject: Re: [SQL] Good Optimization In-reply-to: Your message of Wed, 07 Jul 1999 10:56:26 -0400 <37836A99.6989795E@kearneydev.com> Date: Wed, 07 Jul 1999 11:42:37 -0400 Message-ID: <26200.931362157@sss.pgh.pa.us> From: Tom Lane secret writes: > There is a simple way to optimize SQL queries involving joins to > PostgreSQL that I think should be handled by Postgre? If one is joining > a tables a,b on attribute "x" and if one has something like x=3 then it > helps A LOT to say: a.x=3 and b.x=3 in addition to saying a.x=b.x ... > The example below shoulds the radical speed gain of doing this, and I > think it isn't something real obvious to most people... How much *actual* speedup is there? I don't trust the optimizer's numbers as anything more than relative measures ;-) I'm a bit surprised that you are getting a nested-loop plan and not a merge or hash join. With a merge join, at least, there ought not be a large difference from providing the additional qual clause (I think). What Postgres version are you using? regards, tom lane