Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XgMru-0004AN-II for pgsql-performance@arkaria.postgresql.org; Tue, 21 Oct 2014 00:00:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XgMru-0001WH-00 for pgsql-performance@arkaria.postgresql.org; Tue, 21 Oct 2014 00:00:06 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XgMrs-0001TE-57 for pgsql-performance@postgresql.org; Tue, 21 Oct 2014 00:00:04 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XgMro-0002Oj-RQ for pgsql-performance@postgresql.org; Tue, 21 Oct 2014 00:00:02 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id s9KNxvbr024747; Mon, 20 Oct 2014 19:59:57 -0400 From: Tom Lane To: Marco Di Cesare cc: "pgsql-performance@postgresql.org" Subject: Re: Query with large number of joins In-reply-to: References: Comments: In-reply-to Marco Di Cesare message dated "Mon, 20 Oct 2014 20:32:56 -0000" Date: Mon, 20 Oct 2014 19:59:57 -0400 Message-ID: <24746.1413849597@sss.pgh.pa.us> X-Pg-Spam-Score: -3.3 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Marco Di Cesare writes: > We are using a BI tool that generates a query with an unusually large number of joins. My understanding is that with this many joins Postgres query planner can't possibly use an exhaustive search so it drops into a heuristics algorithm. Unfortunately, the query runs quite slow (~35 seconds) and seems to ignore using primary keys and indexes where available. > Query plan here (sorry had to anonymize): > http://explain.depesz.com/s/Uml It's difficult to make any detailed comments when you've shown us only an allegedly-bad query plan, and not either the query itself or the table definitions. However, it appears to me that the query plan is aggregating over a rather large number of join rows, and there are very few constraints that would allow eliminating rows. So I'm not at all sure there is a significantly better plan available. Are you claiming this query was instantaneous on SQL Server? The only thing that jumps out at me as possibly improvable is that with a further increase in work_mem, you could probably get it to change the last aggregation step from Sort+GroupAggregate into HashAggregate, which'd likely run faster ... assuming you can spare some more memory. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance