Skip site navigation (1) Skip section navigation (2)

Re: analyzer/planner and clustered rows

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Joseph Shraibman <jks(at)selectacast(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: analyzer/planner and clustered rows
Date: 2004-04-30 07:33:13
Message-ID: q0v39094mi9vrab5v0e2nmofv6f3d9l942@email.aon.at (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

pgsql-performance by date

Next:From: JeffDate: 2004-04-30 12:32:16
Subject: Re: planner/optimizer question
Previous:From: Gary DoadesDate: 2004-04-30 07:01:26
Subject: Re: planner/optimizer question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group