Search Postgresql Archives

Re: Increasing statistics results in worse estimates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Shelby Cain <alyandon@xxxxxxxxx> writes:
> --- Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> What did you increase it to, exactly?  Could we see
>> the contents of
>> pg_stats for these two columns at both target
>> settings?

> Generally, the more I increased the stats target the
> better the correlation estimate and the worse the row estimate.

Hm.  I should have realized why correlation wouldn't be high for the
city name: given the ordering by zipcode, city name values may be
pretty well clumped, but they aren't in any kind of alphabetical
order --- and it's the overall ordering, not the clumping, that
correlation measures.

However, there is something absolutely wacko about the stats collection
process here ... you've got fairly reasonable looking results for
most-common-values of city name at the lower end of the stats settings
(HOUSTON and DALLAS are the most common, sounds about right) ... but at
the higher settings the ordering of most-common entries just goes nuts.
We've got some kind of bug there.

What exactly are you changing in the different cases ---
default_statistics_target, or are you doing an ALTER TABLE on some
of the columns (if so which)?

It might be easier to debug this if you could send me the test case.
Any problem with sending just the city name and zipcode columns
of the table (offlist of course)?  COPY TO with a column list can
extract that for you.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux