Re: correlation in pg_stats

>M(at)a@r(at)k wrote
> > Short summary:
> >
> > * It looks to me like the planner vastly overestimates
> > the # of pages read by index scan in quite a few of my
> > tables even though stats collected by ANALYZE are correct.
> >
> > * The problem happens any time you have multiple columns
> > that have a number of repeated values in them, and
> > you CLUSTER the table by a sort using both columns
> > (like "city,state,zip,phone#" or "firstname,lastname").
> >
> > * I think this is the problem that Mark Kirkwood is seeing
> > in his threads Query optimizer 8.0.1 and "One Big trend
> > vs multiple smaller trends" in hackers.
>
> actually pgsql(at)mohawksoft(dot)com, is Mark Woodward. Pleased to meet you.
> :)
>
> (I hate using my name on lists like this because of spammers)
>
> >
> > * A test script demonstrating the issue also follows.
> >
> > * I think keeping one more stat per attribute in
> > pg_stastic that could describe this behavior.
> >
> >
> > Longer:
> >
> >
> > If I understand the optimizer correctly, correlation is used
> > to both guess how much random disk access will be required in
> > a query; as well as estimate how many pages will be read.
> >
> > Unfortunately, many tables in my larger databases have
> > columns with values that are tightly packed on a few pages;
> > even though there is no total-ordering across the whole table.
> > Stephan Szabo described this as a "clumping effect":
> >
> http://archives.postgresql.org/pgsql-performance/2003-01/msg00286.php
>
> Yes.
>
> I think we are describing the exact same issue.
>

I believe that is a very common case.

Many natural value distributions follow the Zipf distribution, which is
commonly known as the 80-20 curve.

Best Regards, SImon Riggs