2017-11-15 20:58 GMT+01:00 Gunther <raj@xxxxxxxx>:
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
