Re: Query with large number of joins - Mailing list pgsql-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
timewe 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