pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
analyzer/planner and clustered rows
2+ messages / 2 participants
[nested] [flat]

* analyzer/planner and clustered rows
@ 2004-04-29 23:09 Joseph Shraibman <jks@selectacast.net>
  2004-04-30 07:33 ` Re: analyzer/planner and clustered rows Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 1 reply; 2+ messages in thread

From: Joseph Shraibman @ 2004-04-29 23:09 UTC (permalink / raw)
  To: pgsql-performance

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?



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: analyzer/planner and clustered rows
  2004-04-29 23:09 analyzer/planner and clustered rows Joseph Shraibman <jks@selectacast.net>
@ 2004-04-30 07:33 ` Manfred Koizar <mkoi-pg@aon.at>
  0 siblings, 0 replies; 2+ messages in thread

From: Manfred Koizar @ 2004-04-30 07:33 UTC (permalink / raw)
  To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-04-30 07:33 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-29 23:09 analyzer/planner and clustered rows Joseph Shraibman <jks@selectacast.net>
2004-04-30 07:33 ` Manfred Koizar <mkoi-pg@aon.at>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox