Re: Re: low performance

Yes, I understand very clearly what you mean.

Maybe my mails were to confused, that's why I try to explain my problem once more:

step 1. An empty table with a primary key (=index key) where an "explain" tells me, that a Seq Scan is used to SELECT a special row.
step 2. Then I start to fill data into that table.
step 3. Then I run a vacuum analyze to update the planner statistics.
step 4. I run an "EXPLAIN select * from <mytable> where <pk-column> = 999;"
step 5. Then I fill in additional data.

What I expect is, that from step 5 on the pk-trigger (I don't know if this mechanism that checks for uniqueness is really a trigger) uses the Index to check for possible double entries.
Although "EXPLAIN" in step 4 pretend to use an Index Scan the data insert becomes slower and slower (>98% of the processor is used by a postmaster). All these steps are done with a single connection (postmaster).

The only way to make it faster after step 3 is to close that connection (and stop that postmaster thread with it) and establish a new one.
It seems like the planner (at least for pk checking) of an *established* connection to a database doesn't receive the information gained from "vacuum analyze".

Greetings
Andreas

On Mon, 03 Sep 2001 12:26:39 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andreas Wernitznig <andreas(at)insilico(dot)com> writes:
> > To make it more comparable I have made two additional runs, a slow and
> > a fast one with exactly the same number of inserts (about 20500) and
> > put it on our ftp server:
>
> >> However, I think what is happening is that some queries are being done
> >> as indexscans in the fast case and seqscans in the slow case. The
> >> ratio of ExecIndexScan calls to ExecSeqScan calls is vastly different
> >> in the two profiles.
>
> > Does the new profiles proof that assumption ?
>
> Yes, see for yourself:
> def.fast:
> 0.00 0.00 0.00 22481 0.00 0.00 ExecSeqScan
> 0.00 0.00 0.00 20161 0.00 0.00 ExecIndexScan
> def.slow:
> 0.00 0.01 0.00 41940 0.00 0.00 ExecSeqScan
> 0.00 0.01 0.00 702 0.00 0.00 ExecIndexScan
>
> So there are about 19500 queries that are being done as indexscans in
> one case and seqscans in the other.
>
> > If I run "vacuum" and "vacuum analyze" on an empty database, the
> > following run will be a SLOW one.
>
> The whole point of vacuum analyze is to give the planner some statistics
> about the contents of the tables. Vacuum analyze when a table is empty
> is useless (even counterproductive, if the table shortly thereafter
> becomes large --- the planner will still think it is empty).
>
> regards, tom lane
>