Re: Re: low performance - Mailing list pgsql-bugs

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
specialrow. 
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
reallya 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
processoris 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
establisha new one. 
It seems like the planner (at least for pk checking) of an *established* connection to a database doesn't receive the
informationgained from "vacuum analyze". 

Greetings
Andreas

On Mon, 03 Sep 2001 12:26:39 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Andreas Wernitznig <andreas@insilico.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
>