Todd A. Cook wrote:
Tom Lane wrote:
"Todd A. Cook" <tcook@xxxxxxxxxxxxxxxxxxxxx> writes:
First, the numbers:
PG Version Load time pg_database_size autovac
----------------------------------------------------------
8.2.13 179 min 92,807,992,820 on
8.3.7 180 min 84,048,744,044 on (defaults)
8.4b2 206 min 84,028,995,344 on (defaults)
8.4b2 183 min 84,028,839,696 off
The bulk of the data is in 16 tables, each having about 55 million rows of
the form (int, int, smallint, smallint, int, int, int). Each table has a
single partial index on one of the integer columns.
Given that it's multiple tables, it'd be possible for autovacuum to
kick in and ANALYZE the data inserted into earlier tables while the
later ones were still being loaded. If so, the discrepancy might be
explained by 8.4's more-aggressive statistics target, which means that
a background ANALYZE will take about 10x more work than before.
If you have time to repeat the experiments, it would be interesting to
see what happens with consistent default_statistics_target across 8.3
and 8.4.
That would seem to be it:
8.4b2 183 min 84,028,897,040 on (defaults, default_statistics_target=10)
I'll run the test on 8.3.7 with default_statistics_target=100 over the weekend.
The results for this are also consistent with Tom's theory:
8.3.7 205 min 84,048,866,924 on (defaults, default_statistics_target=100)
-- todd
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
