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 3B6A2D1B8B0 for ; Thu, 4 Sep 2003 23:16:37 +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 58676-09 for ; Thu, 4 Sep 2003 20:16:29 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id BBA1CD1BA9B for ; Thu, 4 Sep 2003 20:16:25 -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 h84NGGjX011538; Thu, 4 Sep 2003 19:16:16 -0400 (EDT) To: Mary Edie Meredith Cc: pgsql-performance , osdldbt-general Subject: Re: [GENERAL] how to get accurate values in pg_statistic (continued) In-reply-to: <1062697270.6854.3475.camel@ibm-e.pdx.osdl.net> References: <1062697270.6854.3475.camel@ibm-e.pdx.osdl.net> Comments: In-reply-to Mary Edie Meredith message dated "04 Sep 2003 10:41:10 -0700" Date: Thu, 04 Sep 2003 19:16:15 -0400 Message-ID: <11537.1062717375@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/78 X-Sequence-Number: 3444 Mary Edie Meredith writes: > Stephan Szabo kindly responded to our earlier queries suggesting we look > at default_statistics_target and ALTER TABLE ALTER COLUMN SET > STATISTICS. > These determine the number of bins in the histogram for a given column. > But for a large number of rows (for example 6 million) the maximum value > (1000) does not guarantee that ANALYZE will do a full scan of the table. > We do not see a way to guarantee the same statistics run to run without > forcing ANALYZE to examine every row of every table. Do you actually still have a problem with the plans changing when the stats target is above 100 or so? I think the notion of "force ANALYZE to do a full scan" is inherently wrongheaded ... it certainly would not produce numbers that have anything to do with ordinary practice. If you have data statistics that are so bizarre that the planner still gets things wrong with a target of 1000, then I'd like to know more about why. regards, tom lane