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@xxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
