From: Andrew Dunstan <andrew@dunslane.net>
To: Marco Di Cesare <Marco.DiCesare@pointclickcare.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query with large number of joins
Date: Tue, 21 Oct 2014 12:31:06 -0400
Message-ID: <54468A4A.8090908@dunslane.net> (raw)
In-Reply-To: <0a72826fe7d04b71b9ab47d24992d898@CO2PR0701MB759.namprd07.prod.outlook.com>
References: <e187bede699941a4b3fe6781a69ecd14@CO2PR0701MB759.namprd07.prod.outlook.com>
<CAHyXU0y+YqCgizzQicqpaN6surof4DnR6xjTXaunuw5_yjDZJw@mail.gmail.com>
<0a72826fe7d04b71b9ab47d24992d898@CO2PR0701MB759.namprd07.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andrew@dunslane.net, Marco.DiCesare@pointclickcare.com, mmoncure@gmail.com
Subject: Re: Query with large number of joins
In-Reply-To: <54468A4A.8090908@dunslane.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox