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 B86B2D1B9A1 for ; Fri, 5 Sep 2003 20:49:46 +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 24650-07 for ; Fri, 5 Sep 2003 17:49:36 -0300 (ADT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by svr1.postgresql.org (Postfix) with ESMTP id 71619D1B8B3 for ; Fri, 5 Sep 2003 17:49:34 -0300 (ADT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id h85KnCv16810; Fri, 5 Sep 2003 16:49:12 -0400 (EDT) From: Bruce Momjian Message-Id: <200309052049.h85KnCv16810@candle.pha.pa.us> Subject: Re: [GENERAL] how to get accurate values in pg_statistic In-Reply-To: <1062783865.6854.4780.camel@ibm-e.pdx.osdl.net> To: Mary Edie Meredith Date: Fri, 5 Sep 2003 16:49:11 -0400 (EDT) Cc: Tom Lane , pgsql-performance , osdldbt-general X-Mailer: ELM [version 2.4ME+ PL106 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/104 X-Sequence-Number: 3470 Mary Edie Meredith wrote: > I certainly don't claim that it is appropriate to force customers into a > full analysis, particularly if random sampling versus a full scan of the > data reveals little to no performance differences in the plans. Being > able to sample accurately is _very nice for large tables. > > For our testing purposes, however, consistent results are extremely > important. We have observed that small difference in one plan for one of > 22 queries can cause a difference in the DBT-3 results. If this > happens, a small change in performance runs between two Linux kernels > may appear to be due to the kernels, when in fact it is due to the plan > change. > > We know that the plans are _exactly the same if the data in the > pg_statistics table is the same from run to run (all other things being > equal). So what we need to have is identical optimizer costs > (pg_statistics) for the same table data for each. > > I feel certain that the pg_statistics table will be identical from run > to run if analyze looks at every row. Thus our hope to find a way to > get that. Actually, if you are usig GEQO (many tables in a join) the optimizer itself will randomly try plans --- even worse than random statistics. We do have: #geqo_random_seed = -1 # -1 = use variable seed that lets you force a specific random seed for testing purposes. I wonder if that could be extended to control VACUUM radomization too. Right now, it just controls GEQO and in fact gets reset on every optimizer run. I wonder if you could just poke a srandom(10) in src/backend/command/analyze.c. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073