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 <[EMAIL PROTECTED]> wrote:
> Andreas Wernitznig <[EMAIL PROTECTED]> 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
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
