From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ants Aasma <ants.aasma@eesti.ee>
Cc: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad estimation for "where field not in"
Date: Thu, 01 Mar 2012 16:18:00 -0500
Message-ID: <17820.1330636680@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+CSw_vALyg5x-2ynK-tNpgtv9Yw41ZQx=C5enc0QFjzk7A=Dg@mail.gmail.com>
References: <CA+mi_8ZcOCdKuYY+_DazyzxR87Afv-nDPgSdm=C8rurEFJix8w@mail.gmail.com>
<CA+CSw_vALyg5x-2ynK-tNpgtv9Yw41ZQx=C5enc0QFjzk7A=Dg@mail.gmail.com>
Ants Aasma <ants.aasma@eesti.ee> writes:
> On Thu, Mar 1, 2012 at 6:40 PM, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
>> Is this a known planner shortcoming or something unexpected, to be
>> escalated to -bugs? Server version is 9.0.1.
> The relevant code is in scalararraysel() function. It makes the
> assumption that element wise comparisons are completely independent,
> while the exact opposite is true. This has been this way since
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=290166f93404d8759f4bf60ef1732c8ba9...
> introduced it to version 8.2.
> At least for equality and inequality ops it would be good to rework
> the logic to aggregate with
> s1 = s1 + s2 and s1 = s1 + s2 - 1 correspondingly.
Yeah, I was about to make a similar proposal. In principle, when
working with a constant array, we could de-dup the array elements
and then arrive at an exact result ... but that seems like expensive
overkill, and in particular it'd be penalizing intelligently-written
queries (which wouldn't have dups in the array to start with) to benefit
badly-written ones. So it seems like the right thing is for
scalararraysel to (1) check if the operator is equality or inequality,
and if so (2) just assume the array elements are all different and so
the probabilities sum directly. If the operator is something else
it's probably best to stick with the existing logic. We could probably
also protect ourselves a bit more by noting if the sum gives an
impossible result (probability > 1 or < 0) and falling back to the
normal calculation in that case.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ants.aasma@eesti.ee, daniele.varrazzo@gmail.com
Subject: Re: Bad estimation for "where field not in"
In-Reply-To: <17820.1330636680@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox