| 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: | Whole Thread | Raw Message | 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 |