Re: Re: low performance
От | Andreas Wernitznig |
---|---|
Тема | Re: Re: low performance |
Дата | |
Msg-id | 20010903205352.3829a7db.andreas@insilico.com обсуждение исходный текст |
Ответ на | Re: Re: low performance (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | 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 >
В списке pgsql-bugs по дате отправления: