X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 075CED1C514 for ; Thu, 7 Aug 2003 23:32:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 44692-03 for ; Thu, 7 Aug 2003 20:30:45 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id EEFB5D1C488 for ; Thu, 7 Aug 2003 20:31:56 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h77NVqSa009731; Thu, 7 Aug 2003 19:31:52 -0400 (EDT) To: Sean Chittenden Cc: Manfred Koizar , pgsql-performance@postgresql.org Subject: Re: Moving postgresql.conf tunables into 2003... In-reply-to: <20030807202426.GI94710@perrin.int.nxad.com> References: <20030703190502.GR72567@perrin.int.nxad.com> <2422.1057264735@sss.pgh.pa.us> <20030704000646.GV72567@perrin.int.nxad.com> <4nfiiv850cu7vkp39tv7mk2b3pniim8ccu@4ax.com> <20030805222609.GF94710@perrin.int.nxad.com> <20030807202426.GI94710@perrin.int.nxad.com> Comments: In-reply-to Sean Chittenden message dated "Thu, 07 Aug 2003 13:24:26 -0700" Date: Thu, 07 Aug 2003 19:31:52 -0400 Message-ID: <9730.1060299112@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/146 X-Sequence-Number: 2963 Sean Chittenden writes: >> If you CLUSTER on an index and then ANALYSE, you get a correlation of >> 1.0 (== optimum) for the first column of the index. > Correlating of what to what? Of data to nearby data? Of data to > related data (ie, multi-column index?)? Of related data to pages on > disk? Not 100% sure in what context you're using the word > correlation... The correlation is between index order and heap order --- that is, are the tuples in the table physically in the same order as the index? The better the correlation, the fewer heap-page reads it will take to do an index scan. Note it is possible to measure correlation without regard to whether there actually is any index; ANALYZE is simply looking to see whether the values appear in increasing order according to the datatype's default sort operator. One problem we have is extrapolating from the single-column correlation stats computed by ANALYZE to appropriate info for multi-column indexes. It might be that the only reasonable fix for this is for ANALYZE to compute multi-column stats too when multi-column indexes are present. People are used to the assumption that you don't need to re-ANALYZE after creating a new index, but maybe we'll have to give that up. > But that value will degrade after time and at what rate? Does ANALYZE > maintain that value so that it's kept acurrate? You keep it up to date by ANALYZE-ing at suitable intervals. It's no different from any other statistic. regards, tom lane