On 22 Aug 2003, Jenny Zhang wrote:
> 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?
Analyze only does a statistical sampling so it'd be expected that you can
get different results from analyze on the same data. There are ways to
affect the sample size and some other analyze parameters if you're
interested in look at it (default_statistics_target and ALTER TABLE ALTER
COLUMN SET STATISTICS).