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
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 |