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