It works fine now, on my test server execution time went down from 6.4
seconds to 1.4 seconds and on the production server went down from 3.2
sec to 600ms.
To optimize the query I changed the order of some joins(the joins that
where used to limit rows are at the begining of the query)
I tried some of these parameters, I will try all tomorow.
------ Original Message ------
From: "Karl Czajkowski" <karlcz@isi.edu>
To: "Sterpu Victor" <victor@caido.ro>
Cc: "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: 9/5/2016 8:47:12 PM
Subject: Re: Slow query when the select list is big
>On May 09, Sterpu Victor modulated:
>> I tested it now, EXPLAIN ANALYZE is not showing LEFT JOIN at all if
>> I don't select from the joined tables.
>> Now is clear why the query is so mutch more efficient when I select
>> less data.
>>
>> Thank you
>>
>
>With so many joins, you may want to experiment with postgresql
>parameter tuning. These parameters in particular can have a
>significant impact on the plan choice and execution time:
>
> work_mem
> effective_cache_size
>
> from_collapse_limit
> join_collapse_limit
>
> geqo_threshold
> geqo_effort
>
>Setting these to appropriately large values can make analytic queries
>run much faster. Of course, setting them too high can also make for
>very bad plans which cause the DB server to over subscribe its memory
>and start swapping... it requires a bit of reading and a bit of
>experimentation to find ideal settings for your environment.
>
>
>Karl
>