Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 691804769C8 for ; Fri, 24 Jan 2003 02:35:50 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 75741477248 for ; Fri, 24 Jan 2003 01:48:20 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0O6mJ5u011026; Fri, 24 Jan 2003 01:48:19 -0500 (EST) To: Ron Mayer Cc: pgsql-performance@postgresql.org Subject: Re: Does "correlation" mislead the optimizer on large tables? In-reply-to: References: Comments: In-reply-to Ron Mayer message dated "Thu, 23 Jan 2003 20:16:09 -0800" Date: Fri, 24 Jan 2003 01:48:19 -0500 Message-ID: <11025.1043390899@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/277 X-Sequence-Number: 924 Ron Mayer writes: > On a large tables, I think the "correlation" pg_stats field as calculated > by "vacuum analyze" or "analyze" can mislead the optimizer. If you look in the pghackers archives, you will find some discussion about changing the equation that cost_index() uses to estimate the impact of correlation on indexscan cost. The existing equation is ad-hoc and surely wrong, but so far no one's proposed a replacement that can be justified any better. If you've got such a replacement then we're all ears... > In particular, if I have a large table t with columns 'a','b','c', etc, > and I cluster the table as follows: > create table t_ordered as select * from t order by a,b; > vacuum analyze t_ordered; > Column "b" will (correctly) get a very low "correlation" in > the pg_stats table -- but I think the optimizer would do better > assuming a high correlation because similar 'b' values are still > grouped closely on the same disk pages. How would that be? They'll be separated by the stride of 'a'. It seems likely to me that a one-dimensional correlation statistic may be inadequate, but I haven't seen any proposals for better stats. regards, tom lane