From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Nathan Boley <npboley(at)gmail(dot)com> |
Cc: | Greg Stark <gsstark(at)mit(dot)edu>, Josh Berkus <josh(at)agliodbs(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: default_statistics_target WAS: max_wal_senders must die |
Date: | 2010-10-21 02:00:20 |
Message-ID: | AANLkTinRiR-TnNsW7O+S4H=J-RkA+Txj1RcZQJ5oErL9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Oct 20, 2010 at 9:53 PM, Nathan Boley <npboley(at)gmail(dot)com> wrote:
>> Robert explained why having more MCVs might be useful because we use
>> the frequency of the least common MCV as an upper bound on the
>> frequency of any value in the MCV.
>
> Where is that being used?
var_eq_const
> The only non-MCV frequency estimate that I
> recall seeing is ( nrows - n_ndistinct_rows )/ndistinct. Obviously
> changing the number of mcv's affects this by lowering
> n_ndistinct_rows, but it's always pretty coarse estimate.
That one's used, too, but the other is used as an upper bound.
n_distinct tends to come out too small on large tables, so that
formula is prone to overestimation. Actually, both formulas are prone
to overestimation.
>> Binding the length of the MCV list to the size of the histogram is
>> arbitrary but so would any other value
>
> Wouldn't the best approach be to stop adding MCV's/histogram buckets
> when adding new ones doesn't decrease your prediction error
> 'substantially'?
>
> One very hacky threshold heuristic is to stop adding MCV's when a
> simple equality select ( SELECT col FROM table WHERE col == VALUE )
> would switch the plan from an index to a sequential scan ( or vice
> versa, although with the current code this would never happen ). ie,
> if the non_mcv frequency estimate is 0.1% ( producing an index scan ),
When this happens depends on the values of a whole boat-load of GUCs...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-10-21 02:07:43 | Re: lazy snapshots? |
Previous Message | Itagaki Takahiro | 2010-10-21 01:57:15 | Re: Extensions, this time with a patch |