Re: Worse perfomance on 8.2.0 than on 7.4.14

On Fri, 2007-01-05 at 19:28 +0100, Rolf Østvik wrote:
> --- Tom Lane <tgl@sss.pgh.pa.us> skrev:
>
> > The number-of-matching-rows estimate has gone up by a factor of 10,
> > which undoubtedly has a lot to do with the much higher cost estimate.
> > Do you have any idea why that is ... is the table really the same size
> > in both servers?  If so, could we see the pg_stats row for
> > step_result_subset.uut_result on both servers?
>
> Table step_result_subset and uut_result_subset in both databases is created from same schema
> definition file and filled with data from the same data source file.
>
> ==== Server 7.4.14: ====
>
> logistics_74# select count(*) from step_result_subset;
>   count
> ----------
>  17179506
> (1 row)
>
> logistics_74# select count(distinct uut_result) from step_result_subset;
>  count
> --------
>  176450
> (1 row)
>
> logistics_74# analyse verbose step_result_subset;
> INFO:  analyzing "public.step_result_subset"
> INFO:  "step_result_subset": 92863 pages, 3000 rows sampled, 17179655 estimated total rows
> ANALYZE
>
> logistics_74# select * from pg_stats where tablename = step_result_subset and
> attname='uut_result';
>  schemaname |     tablename      |  attname   | null_frac | avg_width | n_distinct |
>          most_common_vals                        |
> most_common_freqs                                               |
> histogram_bounds                             | correlation
>
------------+--------------------+------------+-----------+-----------+------------+----------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
>  public     | step_result_subset | uut_result |         0 |         4 |      57503 |
> {70335,145211,17229,20091,21827,33338,34370,42426,47274,54146} |
> {0.001,0.001,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667,0.000666667}
> | {213,30974,51300,68529,85053,100838,114971,128126,144230,161657,176691} |    0.951364
> (1 row)
>
>
> ==== Server 8.2.0: ====
>
> logistics_82# select count(*) from step_result_subset;
>   count
> ----------
>  17179506
> (1 row)
>
> logistics_82# select count(distinct uut_result) from step_result_subset;
>  count
> --------
>  176450
> (1 row)
>
> logistics_82# analyse verbose step_result_subset;
> INFO:  analyzing "public.step_result_subset"
> INFO:  "step_result_subset": scanned 3000 of 92863 pages, containing 555000 live rows and 0 dead
> rows; 3000 rows in sample, 17179655 estimated total rows
> ANALYZE
>
> logistics_# select * from pg_stats where tablename = step_result_subset and attname='uut_result';
>  schemaname |     tablename      |  attname   | null_frac | avg_width | n_distinct |
>          most_common_vals                         |
> most_common_freqs                                                |
> histogram_bounds                             | correlation
>
------------+--------------------+------------+-----------+-----------+------------+-----------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------+-------------
>  public     | step_result_subset | uut_result |         0 |         4 |       6516 |
> {35010,111592,35790,41162,56844,57444,60709,73017,76295,106470} |
> {0.00166667,0.00166667,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333,0.00133333}
> | {147,31791,54286,70928,85996,102668,117885,130947,144766,162098,176685} |    0.954647
> (1 row)
>
> Then on server 8.2.0 i need to set statistics to ~120 on step_result_subset.uut_result to get
> n_distinct to be in same range as n_distinct on 7.4.14.
>
> Even with a statistics value of 1000, the n_distinct value does only reach ~138 000. Is it correct
> that _ideally_ the n_distinct value should be the same as "select count(distinct uut_result) from
> step_result_subset"?

That is correct, as long as the number hasn't changed between the
ANALYZE and the select.

> Even with better statistics on step_result_subset.uut_result neither of 7.4.14 or 8.2.0 manages to
> pick the best plan when i want to select bigger datasets (in my examples that would be to set an
> earlier date in the where clause for "ur.start_date_time > '2006-12-11'"). I will continue to
> adjust other parameters and see what i can manage myself.

The ndistinct figure is very sensitive.

Could you re-run ANALYZE say 10 times each on the two release levels?
That will give you a better feel for the spread of likely values.

The distribution of rows with those values also makes a difference to
the results. ANALYZE assumes that all values are randomly distributed
within the table, so if the values are clumped together for whatever
reason the ndistinct calc is less likely to take that into account.

The larger sample size gained by increasing stats target does make a
difference.

--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com