Message-ID: <5513FF23.8090603@2ndquadrant.com>
Date: Thu, 26 Mar 2015 13:44:19 +0100
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Organization: 2ndQuadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:31.0) Gecko/20100101 Thunderbird/31.5.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Index only scan sometimes switches to sequential scan
 for small amount of rows
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>
In-Reply-To: 
 <CAMkU=1zxyNMN11YL8G7AGF7k5u4ZHVJN0DqCc_ecO1qs49uJgA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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