Joseph S <jks(at)selectacast(dot)net> wrote:
> I just installed a shiny new database server with pg 8.4.1 running
> on CentOS 5.4. After using slony to replicate over my database I
> decided to do some basic performance tests to see how spiffy my
> shiny new server is. This machine has 32G ram, over 31 of which
> is used for the system file cache.
>
> So I run "select count(*) from large_table" and I see in xosview a
> solid block of write activity. Runtime is 28125.644 ms for the
> first run. The second run does not show a block of write activity
> and takes 3327.441 ms
As others have mentioned, this is due to hint bit updates, and doing
an explicit VACUUM after the load and before you start using the
database will avoid run-time issues. You also need statistics, so
be sure to do VACUUM ANALYZE.
There is one other sneaky surprise awaiting you, however. Since
this stuff was all loaded with a narrow range of transaction IDs,
they will all need to be frozen at about the same time; so somewhere
down the road, either during a routine database vacuum or possibly
in the middle of normal operations, all of these rows will need to
be rewritten *again* to change the transaction IDs used for managing
MVCC to the special "frozen" value. We routinely follow a load with
VACUUM FREEZE ANALYZE of the database to combine the update to
freeze the tuples with the update to set the hint bits and avoid
this problem.
There has been some talk about possibly writing tuples in a frozen
state with the hint bits already set if they are loaded in the same
database transaction which creates the table, but I'm not aware of
anyone currently working on this.
-Kevin