Ofer Israeli <oferi@xxxxxxxxxxxxxx>
wrote:
>Hi all,
> In performance testing we’re doing we are
currently running two scenarios:
> 1. Starting from an empty
db, therefore all operations are INSERTs.
> 2. Starting from an existing db – thereby
UPDATing all records.
> I should also mention that the
tables we are dealing with are heavily indexed.
> I would expect that the first scenario
would give much better results than the second one
as:
> 1. INSERT
should be cheaper than UPDATE due to only dealing with one record instead of
two.
> 2. We also have SELECT queries before
the operation and in the first configuration, the SELECTs will be dealing with
much less data for most of the run.
> To our surprise, we see that the second
scenario gives better results with an average processing time of an event at
around %70 of the time run in the first scenario.
> Anyone have any ideas on why the empty db
is giving worse results??
A little googleing led me to this thought, will be
happy to hear you're input on this. If the database is initially empty,
the analyzer will probably decide to query the tables by full table scan as
opposed to index searching. Now supposedly, during our test run, the
analyzer does not run frequently enough and so at some point we are using the
wrong method for SELECTs.
The version we are using is
8.3.7.
My questions are:
1. Does the above seem reasonable to
you?
2. How often does the analyzer run? To my
understanding it will always run with autovacuum, is that right? Is it
triggered at other times as well?
3. Does the autoanalyzer work only partially on the db
like autovacuum going to sleep after a certain amount of work was done or does
it work until finished? If it is partial work, maybe it does not reach our
relevant tables. What dictates the order in which it will
work?
Many thanks,
Ofer
| 