Re: analyzer/planner and clustered rows

On Thu, 29 Apr 2004 19:09:09 -0400, Joseph Shraibman
<jks(at)selectacast(dot)net> wrote:
>How does the analyzer/planner deal with rows clustered together?

There's a correlation value per column. Just try

SELECT attname, correlation
FROM pg_stats
WHERE tablename = '...';

if you are interested. It indicates how well the hypothetical order of
tuples if sorted by that column corresponds to the physical order. +1.0
is perfect correlation, 0.0 is totally chaotic, -1.0 means reverse
order. The optimizer is more willing to choose an index scan if
correlation for the first index column is near +/-1.

> What if the data in the table happens to be close
>together because it was inserted together originally?

Having equal values close to each other is not enough, the values should
be increasing, too. Compare

5 5 5 4 4 4 7 7 7 2 2 2 6 6 6 3 3 3 8 8 8 low correlation
and
2 2 2 3 3 3 4 4 4 5 5 5 6 6 6 7 7 7 8 8 8 correlation = 1.0