On Thu, 29 Apr 2004 19:09:09 -0400, Joseph Shraibman
[EMAIL PROTECTED] 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
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
