Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D80B4476547 for ; Thu, 16 Jan 2003 12:30:08 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id BF3784764AB for ; Thu, 16 Jan 2003 12:30:07 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0GHU95u016506; Thu, 16 Jan 2003 12:30:09 -0500 (EST) To: "Josh Berkus" Cc: "Roman Fail" , sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org Subject: Re: 7.3.1 New install, large queries are slow In-reply-to: References: Comments: In-reply-to "Josh Berkus" message dated "Thu, 16 Jan 2003 09:16:33 -0800" Date: Thu, 16 Jan 2003 12:30:09 -0500 Message-ID: <16505.1042738209@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/162 X-Sequence-Number: 809 "Josh Berkus" writes: > This could be re-arranged some, but I think you get the idea ... I've > been able, in some queries, to get the planner to use a better and > faster join strategy by repeating my WHERE conditions in the JOIN > criteria. Hm. It shouldn't be necessary to do that --- the planner should be able to push down the WHERE conditions to the right place without that help. The list of explicit JOINs as you have here is a good way to proceed *if* you write the JOINs in an appropriate order for implementation. I believe the problem with Roman's original query was that he listed the JOINs in a bad order. Unfortunately I didn't keep a copy of that message, and the list archives seem to be a day or more behind... but at least for these WHERE conditions, it looks like the best bet would to join m to b (I'm assuming m.merchid is unique), then to t, then to d, then add on the others. regards, tom lane