Message-ID: <54468A4A.8090908@dunslane.net>
Date: Tue, 21 Oct 2014 12:31:06 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: Marco Di Cesare <Marco.DiCesare@pointclickcare.com>,
 Merlin Moncure <mmoncure@gmail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Query with large number of joins
References: 
 <e187bede699941a4b3fe6781a69ecd14@CO2PR0701MB759.namprd07.prod.outlook.com>
 <CAHyXU0y+YqCgizzQicqpaN6surof4DnR6xjTXaunuw5_yjDZJw@mail.gmail.com>
 <0a72826fe7d04b71b9ab47d24992d898@CO2PR0701MB759.namprd07.prod.outlook.com>
In-Reply-To: 
 <0a72826fe7d04b71b9ab47d24992d898@CO2PR0701MB759.namprd07.prod.outlook.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 10/21/2014 12:09 PM, Marco Di Cesare wrote:
> I did not mean to imply this works any better on SQL Server. We never tried. I just meant to say this is the first time we are using Postgres so we don't have much experience with it.
>
> We tried with work_mem set to 1GB (even as high as 3GB) but it didn't change the GroupAggregate and Sort or query run time.
>
> Sorry, I had to sanitize the query and a few of the relevant tables so hopefully I got it all right.
>
> SELECT
>                  "foxtrot_india"."juliet_alpha",
>                  "foxtrot_india"."foxtrot_yankee",
>                  "foxtrot_india"."hotel_sierra",
>                  "foxtrot_india"."juliet_alpha",
> 				"foxtrot_india"."bravo_romeo",
>                  "oscar_bravo"."golf_foxtrot",
>                  "seven_kilo"."november_lima",
>                  "foxtrot_india"."echo_six",
>                  "uniform_six"."seven_six",
> 				"oscar_charlie"."foxtrot_charlie",
>                  COUNT(DISTINCT "foxtrot_india"."bravo_romeo")
> FROM
>                  "public"."seven_kilo" "seven_kilo"
>                  INNER JOIN "public"."papa_sierra" "papa_sierra" ON ("seven_kilo"."golf_bravo" = "papa_sierra"."golf_bravo")
>                  LEFT JOIN "public"."golf_two" "golf_two" ON ("seven_kilo"."lima" = "golf_two"."lima")
>                  LEFT JOIN "public"."bravo_xray" "bravo_xray" ON ("seven_kilo"."lima" = "bravo_xray"."lima")
>                  LEFT JOIN "public"."foo1" "foo1" ON (("seven_kilo"."bar1" = "foo1"."bar1") AND ("seven_kilo"."golf_bravo" = "foo1"."golf_bravo"))
>                  INNER JOIN "public"."oscar_charlie" "oscar_charlie" ON ("seven_kilo"."lima" = "oscar_charlie"."lima")
>                  INNER JOIN "public"."oscar_bravo" "oscar_bravo" ON ("oscar_charlie"."foxtrot_four" = "oscar_bravo"."foxtrot_four")
>                  INNER JOIN "public"."foxtrot_india" "foxtrot_india" ON ("oscar_bravo"."sierra" = "foxtrot_india"."sierra")
>                  INNER JOIN "public"."hotel_romeo" "hotel_romeo" ON ("oscar_charlie"."foxtrot_charlie" = "hotel_romeo"."foxtrot_charlie")
>                  INNER JOIN "public"."uniform_six" "uniform_six" ON ("hotel_romeo"."hotel_lima" = "uniform_six"."hotel_lima")
>                  LEFT JOIN "public"."lookup" "foo2" ON ("foxtrot_india"."bar2" = "foo2"."lookup_id")
>                  LEFT JOIN "public"."uniform_two" "uniform_two" ON ("foxtrot_india"."sierra" = "uniform_two"."sierra")
>                  INNER JOIN "public"."lookup" "four_xray" ON ("uniform_two"."quebec" = "four_xray"."quebec")
>                  LEFT JOIN "public"."papa_four" "papa_four" ON ("foxtrot_india"."sierra" = "papa_four"."sierra")
>                  INNER JOIN "public"."lookup" "romeo_bravo" ON ("papa_four"."quebec" = "romeo_bravo"."quebec")
>                  LEFT JOIN "public"."juliet_two" "juliet_two" ON ("foxtrot_india"."sierra" = "juliet_two"."sierra")
>                  INNER JOIN "public"."lookup" "four_delta" ON ("juliet_two"."quebec" = "four_delta"."quebec")
>                  LEFT JOIN "public"."foo3" "foo3" ON ("foxtrot_india"."bar3" = "foo3"."bar3")
>                  INNER JOIN "public"."xray" "xray" ON ("seven_kilo"."lima" = "xray"."lima")
>                  INNER JOIN "public"."romeo_echo" "romeo_echo" ON ("xray"."echo_sierra" = "romeo_echo"."echo_sierra")
> WHERE
>                  ((("xray"."echo_sierra" = 'november_foxtrot')
>                  AND ("romeo_echo"."hotel_oscar" = 'zulu')
>                  AND ("oscar_charlie"."five" = 6)
>                  AND ("oscar_charlie"."whiskey" = 'four_romeo')
>                  AND ("oscar_charlie"."charlie_romeo" = 2014)))
> GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10


Please don't top-post on the PostgreSQL lists. See 
<http://idallen.com/topposting.html>

Have you tried a) either turning off geqo or setting geqo_threshold 
fairly high b) setting join_collapse_limit fairly high (assuming all the 
above join targets are tables and not views, setting it to something 
like 25 should do the trick.

You also haven't told us what settings you have for things like 
effective_cache_size, which can dramatically affect query plans.

cheers

andrew


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance