Re: [PERFORM] query performance issue

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: [PERFORM] query performance issue
Дата
Msg-id CAFj8pRDxs5i5TP9Katti=dZFo80eRZX2TyoHLABgmxYwYEt6xA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [PERFORM] query performance issue  (Gunther <raj@gusw.net>)
Список pgsql-performance
Дерево обсуждения


2017-11-15 20:58 GMT+01:00 Gunther <raj@gusw.net>:

On 11/15/2017 8:12, Pavel Stehule wrote:
There is wrong plan due wrong estimation

for this query you should to penalize nested loop

set enable_nestloop to off;

before evaluation of this query

You are not the only one with this issue. May I suggest to look at this thread a little earlier this month.

http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-other-horrible-planner-choices-tp5990160.html

where this has been discussed in some length.

It is typical issue. The source of these problems are correlations between columns (it can be fixed partially by multicolumn statistics in PostgreSQL 10). Another problem is missing multi table statistics - PostgreSQL planner expects so any value from dictionary has same probability, what is not usually true. Some OLAP techniques like calendar tables has usually very bad impact on estimations with this results.

Regards

Pavel


regards,
-Gunther



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