how to get accurate values in pg_statistic

From: Jenny Zhang <jenny(at)osdl(dot)org>
To: Postgresql General <pgsql-general(at)postgresql(dot)org>
Cc: osdldbt-general(at)lists(dot)sourceforge(dot)net
Subject: how to get accurate values in pg_statistic
Date: 2003-08-22 21:13:19
Message-ID: 1061586799.6201.378.camel@ibm-a
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

Last week I reported getting different execution plans for the same
query against the same database. I did further investigation. After
building the database, I did:
vacuumdb -z DBT3
psql DBT3 -c "analyze supplier"
psql DBT3 -c "analyze part"
psql DBT3 -c "analyze partsupp"
psql DBT3 -c "analyze customer"
psql DBT3 -c "analyze orders"
psql DBT3 -c "analyze lineitem"
psql DBT3 -c "analyze nation"
psql DBT3 -c "analyze region"

Then I check the data in pg_class and pg_statistic and get the execution
plans.

I did this twice (say run_8 and run_9). I found that while the pg_class
are the same for run_8 and run_9, the pg_statistic output are
different. I posted the query, the execution plan, pg_class and
pg_statistic output at:
http://www.osdl.org/archive/jenny/

The difference in pg_statistic results in a different execution plan for
query 18.sql. In fact, I updated the pg_statistic table for run_9 with
the values got from run_8, then it gives me the run_8 execution plan,
and the cost is very close(within 1). This makes me to believe that the
optimizer picks the plan based on the values in pg_statistic and
pg_class.

But why the pg_statistic value changes each time I build the database?
Did I do something wrong?

Thanks for your help,
Jenny

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dennis Gearon 2003-08-22 21:18:19 The ..... worm
Previous Message Tom Lane 2003-08-22 20:43:52 Re: WAL Files checkpoint_timeout with voluminous delete/insert