Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e8DFbhs33304 for ; Wed, 13 Sep 2000 11:37:43 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e8DFao407869; Wed, 13 Sep 2000 11:36:50 -0400 (EDT) To: Meszaros Attila cc: pgsql-sql@hub.org Subject: Re: Optimizing Multiply Joins ??? In-reply-to: References: Comments: In-reply-to Meszaros Attila message dated "Wed, 13 Sep 2000 16:16:57 +0200" Date: Wed, 13 Sep 2000 11:36:50 -0400 Message-ID: <7866.968859410@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200009/88 Meszaros Attila writes: > Until we join no more than 10 tables the response time is below 0.2 s. > joining the 11th table comes with a dramatic change: response time > usually grows up to 5-7 s, That's interesting; apparently the regular optimizer is faster than the GEQO optimizer for your style of query. Try increasing the GEQO threshold (pg_option "geqo_rels") to more than 11. > I have examined the output of explain in all 3 cases, and I have > the feeling that the planner simply forgets the best solutions > in 2nd and 3rd case. The GEQO planner does not guarantee to find an optimal solution, it just does a random search through a limited number of possible solutions and uses the best one it happened across. > But I think (hope) we could have a solution, because all of our > complex joins are following foreign keys. Actually, as the 7.1 code currently stands, a query that uses explicit JOIN operators like yours does will always be implemented in exactly the given join order, with no searching. I haven't quite decided if that's a bug or a feature ... regards, tom lane