X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 71D4DD1DB53 for ; Thu, 18 Mar 2004 05:03:55 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 01001-08 for ; Thu, 18 Mar 2004 01:03:50 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id B0B7BD1E883 for ; Thu, 18 Mar 2004 01:03:48 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i2I53lV9004895; Thu, 18 Mar 2004 00:03:47 -0500 (EST) To: Kris Jurka Cc: pgsql-performance@postgresql.org Subject: Re: severe performance issue with planner (fwd) In-reply-to: References: Comments: In-reply-to Kris Jurka message dated "Wed, 17 Mar 2004 02:33:44 -0500" Date: Thu, 18 Mar 2004 00:03:47 -0500 Message-ID: <4894.1079586227@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/253 X-Sequence-Number: 6109 Kris Jurka writes: > On Thu, 11 Mar 2004, Tom Lane wrote: >> "Eric Brown" writes: >>> [ planning a 9-table query takes too long ] >> >> See http://www.postgresql.org/docs/7.4/static/explicit-joins.html >> for some useful tips. > Is this the best answer we've got? For me with an empty table this query > takes 4 seconds to plan, is that the expected planning time? I know I've > got nine table queries that don't take that long. The problem with this example is that it's a nine-way self-join. Ordinarily the planner can eliminate many possible join paths at low levels, because they are more expensive than other available options. But in this situation all the available options have *exactly the same cost estimate* because they are all founded on exactly the same statistics. The planner fails to prune any of them and ends up making a random choice after examining way too many alternatives. Maybe we should think about instituting a hard upper limit on the number of alternatives considered. But I'm not sure what the consequences of that would be. In the meantime, the answer for the OP is to arbitrarily limit the number of join orders considered, as described in the above-mentioned web page. With the given query constraints there's really only one join order worth thinking about ... > Setting geqo_threshold less than 9, it takes 1 second to plan. Does this > indicate that geqo_threshold is set too high, or is it a tradeoff between > planning time and plan quality? Selecting the GEQO planner doesn't really matter here, because it has no better clue about how to choose among a lot of alternatives with identical cost estimates. regards, tom lane