Thread

1. analyzer/planner and clustered rows

   Joseph Shraibman <jks@selectacast.net> — 2004-04-29T23:09:09Z

   How does the analyzer/planner deal with rows clustered together?  Does 
   it just assume that if this col is clustered on then the actual data 
   will be clustered?  What if the data in the table happens to be close 
   together because it was inserted together originally?
   
   

2. Re: analyzer/planner and clustered rows

   Manfred Koizar <mkoi-pg@aon.at> — 2004-04-30T07:33:13Z

   On Thu, 29 Apr 2004 19:09:09 -0400, Joseph Shraibman
   <jks@selectacast.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