Re: Aggregate and many LEFT JOIN

Поиск
Список
Период
Сортировка
От kimaidou
Тема Re: Aggregate and many LEFT JOIN
Дата
Msg-id CAMKXKO4qEWi81UFHUQ5b=Aty-EDLkVf74FoBhFqTrwdUGHKMHg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Aggregate and many LEFT JOIN  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Aggregate and many LEFT JOIN  (kimaidou <kimaidou@gmail.com>)
Re: Aggregate and many LEFT JOIN  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-performance
Дерево обсуждения
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 :

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 :

* 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@sss.pgh.pa.us> a écrit :
Michael Lewis <mlewis@entrata.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Justin Pryzby
Дата:
Сообщение: Re: Massive parallel queue table causes index deterioration, butREINDEX fails with deadlocks.
Следующее
От: kimaidou
Дата:
Сообщение: Re: Aggregate and many LEFT JOIN