pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / 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