X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CADFA5288E for ; Thu, 2 Jun 2005 13:26:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49576-10 for ; Thu, 2 Jun 2005 16:26:25 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id E8FED528CB for ; Thu, 2 Jun 2005 13:26:22 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j52GQQxo014835; Thu, 2 Jun 2005 12:26:26 -0400 (EDT) To: Richard Huxton Cc: philb@vodafone.ie, pgsql-performance@postgresql.org Subject: Re: Query plan for very large number of joins In-reply-to: <429F2D94.3030805@archonet.com> References: <17165144.1117725925709.JavaMail.tomcat@iecsai19> <429F2D94.3030805@archonet.com> Comments: In-reply-to Richard Huxton message dated "Thu, 02 Jun 2005 17:02:28 +0100" Date: Thu, 02 Jun 2005 12:26:26 -0400 Message-ID: <14834.1117729586@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/48 X-Sequence-Number: 12686 Richard Huxton writes: > philb@vodafone.ie wrote: >> I am using PostgreSQL (7.4) with a schema that was generated >> automatically (using hibernate). The schema consists of about 650 >> relations. One particular query (also generated automatically) >> consists of left joining approximately 350 tables. > May I be the first to offer an "ouch"! Seconded. > However, I'm not sure how much leeway there is in > planning a largely left-joined query. Not much. The best hope for a better result is to order the LEFT JOIN clauses in a way that will produce a good plan. One thought is that I am not sure I believe the conclusion that planning is taking only 36 ms; even realizing that the exclusive use of left joins eliminates options for join order, there are still quite a lot of plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE from psql and see how long each takes. It'd also be interesting to keep an eye on how large the backend process grows while doing this --- maybe it's being driven into swap. Also: I'm not sure there *is* such a thing as a good plan for a 350-way join. It may be time to reconsider your data representation. If Hibernate really forces this on you, it may be time to reconsider your choice of tool. regards, tom lane