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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
