Re: Aggregate and many LEFT JOIN

Thanks for your answers. I tried with
> set session work_mem='250MB';
> set session geqo_threshold = 20;
> set session join_collapse_limit = 20;

It seems to have no real impact :
https://explain.depesz.com/s/CBVd

Indeed an index cannot really be used for sorting here, based on the
complexity of the returned fields.
Wich strikes me is that if I try to simplify it a lot, removing all data
but the main table (occtax.observation) primary key cd_nom and aggregate,
the query plan should be able tu use the cd_nom index for sorting and
provide better query plan (hash aggregate), but it does not seems so :

* SQL ; http://paste.debian.net/hidden/c3ee7889/
* EXPLAIN : https://explain.depesz.com/s/FR3h -> a group aggregate is used,
which : GroupAggregate 1 10,639.313 ms 72.6 %

It is better, but I think 10s for such a query seems bad perf for me.

Regards
Michaël

Le ven. 22 févr. 2019 à 19:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> Michael Lewis <mlewis(at)entrata(dot)com> writes:
> > Does the plan change significantly with this-
> > set session work_mem='250MB';
> > set session geqo_threshold = 20;
> > set session join_collapse_limit = 20;
>
> Yeah ... by my count there are 16 tables in this query, so raising
> join_collapse_limit to 15 is not enough to ensure that the planner
> considers all join orders. Whether use of GEQO is a big problem
> is harder to say, but it might be.
>
> regards, tom lane
>