MIME-Version: 1.0
In-Reply-To: <31c10833-9d19-dfa5-1cab-43eaae097828@gusw.net>
References: 
 <CAA=to3gXpr94TqSAYXT9zwxYwY_xKrLFnu8ZJNgJKP-kWza2CA@mail.gmail.com>
 <CAFj8pRCyvCktcxtEQrmF4kw5oR1DNYd_4gJNd6mkjrHw_NPPPw@mail.gmail.com>
 <CAA=to3gnNReZ62m3K3qbN_KL8Hnmrg4wfb4LefZYqUa9v4OOSQ@mail.gmail.com>
 <CAFj8pRC6xh=HDq1jwxW8n1Ct8=FbbdqHcx_VFHNCdNUz41rTWQ@mail.gmail.com>
 <31c10833-9d19-dfa5-1cab-43eaae097828@gusw.net>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 15 Nov 2017 21:07:01 +0100
Message-ID: 
 <CAFj8pRDxs5i5TP9Katti=dZFo80eRZX2TyoHLABgmxYwYEt6xA@mail.gmail.com>
Subject: Re: query performance issue
To: Gunther <raj@gusw.net>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="94eb2c1cb93e60e83e055e0b0e3e"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--94eb2c1cb93e60e83e055e0b0e3e
Content-Type: text/plain; charset="UTF-8"

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
>
>
>

--94eb2c1cb93e60e83e055e0b0e3e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><br><div class=3D"gmail_extra"><br><div class=3D"gmail_quo=
te">2017-11-15 20:58 GMT+01:00 Gunther <span dir=3D"ltr">&lt;<a href=3D"mai=
lto:raj@gusw.net" target=3D"_blank">raj@gusw.net</a>&gt;</span>:<br><blockq=
uote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc =
solid;padding-left:1ex">
 =20
   =20
 =20
  <div text=3D"#000000" bgcolor=3D"#FFFFFF">
    <br>
    <div class=3D"m_-8834044617112416657moz-cite-prefix">On 11/15/2017 8:12=
, Pavel Stehule
      wrote:<br>
    </div>
    <blockquote type=3D"cite">
      <div dir=3D"ltr">
        <div class=3D"gmail_extra">
          <div class=3D"gmail_quote">There is wrong plan due wrong
            estimation <br>
            <div><br>
            </div>
            <div>for this query you should to penalize nested loop</div>
            <div><br>
            </div>
            <div>set enable_nestloop to off;</div>
            <div><br>
            </div>
            <div>before evaluation of this query</div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    You are not the only one with this issue. May I suggest to look at
    this thread a little earlier this month.<br>
    <br>
<a class=3D"m_-8834044617112416657moz-txt-link-freetext" href=3D"http://www=
.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-=
seq-scans-or-other-horrible-planner-choices-tp5990160.html" target=3D"_blan=
k">http://www.postgresql-archive.<wbr>org/OLAP-reporting-queries-<wbr>fall-=
into-nested-loops-over-<wbr>seq-scans-or-other-horrible-<wbr>planner-choice=
s-tp5990160.html</a><br>
    <br>
    where this has been discussed in some length.<br></div></blockquote><di=
v><br></div><div>It is typical issue. The source of these problems are corr=
elations between columns (it can be fixed partially by multicolumn statisti=
cs in PostgreSQL 10). Another problem is missing multi table statistics - P=
ostgreSQL planner expects so any value from dictionary has same probability=
, what is not usually true. Some OLAP techniques like calendar tables has u=
sually very bad impact on estimations with this results.</div><div><br></di=
v><div>Regards</div><div><br></div><div>Pavel<br></div><div> <br></div><blo=
ckquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #c=
cc solid;padding-left:1ex"><div text=3D"#000000" bgcolor=3D"#FFFFFF">
    <br>
    regards,<br>
    -Gunther<br>
    <br>
    <br>
  </div>

</blockquote></div><br></div></div>

--94eb2c1cb93e60e83e055e0b0e3e--