From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Index only scan sometimes switches to sequential scan for small amount of rows
Date: Thu, 26 Mar 2015 13:44:19 +0100
Message-ID: <5513FF23.8090603@2ndquadrant.com> (raw)
In-Reply-To: <CAMkU=1zxyNMN11YL8G7AGF7k5u4ZHVJN0DqCc_ecO1qs49uJgA@mail.gmail.com>
References: <CAK_s-G2tc8r0N3AqPr8fW5QsRQMbZNurgAQ=_ME1aaf4vOmnnA@mail.gmail.com>
<5512ADD2.7080402@2ndquadrant.com>
<CAK_s-G0O4VK-J-9BQtShU1_Osr3NG1_T7VDDXRniXTd9VYPdHw@mail.gmail.com>
<7439.1427304380@sss.pgh.pa.us>
<CAK_s-G27W_WuB-OsEcTiacwhk8i764pQFHMjOKSBnyz+N27T3A@mail.gmail.com>
<CAMkU=1zxyNMN11YL8G7AGF7k5u4ZHVJN0DqCc_ecO1qs49uJgA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 26.3.2015 08:48, Jeff Janes wrote:
>
> OK, this is starting to look like a long-standing bug to me.
>
> If it only sees 3 distinct values, and all three are present at least
> twice, it throws all of them into the MCV list. But if one of those 3
> were present just once, then it tests them to see if they qualify.
> The test for inclusion is that it has to be present more than once,
> and that it must be "over-represented" by 25%.
>
> Lets say it sampled 30000 rows and found 29,900 of one value, 99 of
> another, and 1 of a third.
>
> But that turns into the second one needing to be present 12,500 times.
> The average value is present 10,000 times (30,000 samples with 3
> distinct values) and 25 more than that is 12,500. So it excluded.
>
> It seems to me that a more reasonable criteria is that it must be
> over-represented 25% compared to the average of all the remaining values
> not yet accepted into the MCV list. I.e. all the greater ones should be
> subtracted out before computing the over-representation threshold.
That might work IMO, but maybe we should increase the coefficient a bit
(say, from 1.25 to 2), not to produce needlessly long MCV lists.
> It is also grossly inconsistent with the other behavior. If they are
> "29900; 98; 2" then all three go into the MCV.
Isn't the mincount still 12500? How could all three get into the MCV?
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: tomas.vondra@2ndquadrant.com
Subject: Re: Index only scan sometimes switches to sequential scan for small amount of rows
In-Reply-To: <5513FF23.8090603@2ndquadrant.com>
* 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