pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mary Edie Meredith <maryedie@osdl.org>
To: pgsql-performance <pgsql-performance@postgresql.org>
Cc: osdldbt-general <osdldbt-general@lists.sourceforge.net>
Subject: [GENERAL] how to get accurate values in pg_statistic (continued)
Date: 04 Sep 2003 10:41:10 -0700
Message-ID: <1062697270.6854.3475.camel@ibm-e.pdx.osdl.net> (raw)

Our port of OSDL DBT3 test suite to PostgreSQL (see Background
information below) is nearing completion.  We would also like to confirm
our understanding of an outstanding consistency issue.

We have not been able to do meaningful kernel testing since the runs
(all parameters/kernels being equal) arewildly varying - sometimes
20-25% differences in the metrics run to run. 

We found plans were changing from test run to test run. In one case a
plan ran 20 minutes in the throughput test of one run, and 2 seconds in
another run!  By forcing the contents of pg_statistics to be the same
before the queries run, we have consistent results now. So we know for
sure the problem is due to the random nature of the stats sampling: the
optimizer always saw different stats data resulting in different plans. 

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.  

Are we wrong in our analysis?

Are there main-stream alternatives we have missed? 
 
How do you do testing on large tables and make the execution plans
consistent?

Is there a change to ANALYZE in 7.4 that solves our problem?

TIA.


********************************************************************
Background information:

Database Test 3 (DBT-3) is a decision support workload.

The test kit itself has been executing on PostgreSQL for some time, is
available on sourceforge, and is implemented on our Scalable Test
Platform (STP). 


A bit of background:  The test 
(1) builds a database from load files, gathers statistics, 
(2) runs a single stream of 22 queries plus a set of inserts and deletes
(the power test), then 
(3) runs a multiple stream of the queries with one added stream of
inserts/deletes (the throughput test). 


-- 
Mary Edie Meredith <maryedie@osdl.org>
Open Source Development Lab




view thread (22+ messages)  latest in thread

Message-ID: <1062697270.6854.3475.camel@ibm-e.pdx.osdl.net>
Permalink:  ../1062697270.6854.3475.camel@ibm-e.pdx.osdl.net/
Also on:    postgresql.org/message-id/1062697270.6854.3475.camel@ibm-e.pdx.osdl.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: maryedie@osdl.org, osdldbt-general@lists.sourceforge.net
  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>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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