Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id AE3C12E2002 for ; Wed, 14 Nov 2007 17:08:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 78561-06 for ; Wed, 14 Nov 2007 17:08:00 -0400 (AST) Received: from vms048pub.verizon.net (vms048pub.verizon.net [206.46.252.48]) by postgresql.org (Postfix) with ESMTP id 709E72E1A8C for ; Wed, 14 Nov 2007 17:08:04 -0400 (AST) Received: from [192.168.0.251] ([71.251.196.74]) by vms048.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JRI0015GLCO33A0@vms048.mailsrvcs.net> for pgsql-performance@postgresql.org; Wed, 14 Nov 2007 15:07:38 -0600 (CST) Date: Wed, 14 Nov 2007 16:07:36 -0500 From: Jean-David Beyer Subject: Re: Curious about dead rows. In-reply-to: <473B4AB4.70506@sime.com> To: pgsql performance Message-id: <473B6398.9030507@verizon.net> Organization: Institute for Regimented Whimsey MIME-version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Enigmail-Version: 0.94.0.0 References: <4735DBDB.5050208@verizon.net> <473B4AB4.70506@sime.com> User-Agent: Thunderbird 1.5.0.9 (X11/20070317) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/219 X-Sequence-Number: 27634 Mario Weilguni wrote: > Jean-David Beyer schrieb: >> I am doing lots of INSERTs on a table that starts out empty (I did a >> TRUNCATE on it). I am not, AFAIK, doing DELETEs or UPDATEs. Autovacuum is >> on. I moved logging up to debug2 level to see what was going on, and I >> get >> things like this: >> >> "vl_as": scanned 3000 of 5296 pages, containing 232944 live rows and >> 1033 >> dead rows; 3000 rows in sample, 411224 estimated total rows >> >> A little later, it says: >> >> "vl_as": scanned 3000 of 6916 pages, containing 233507 live rows and 493 >> dead rows; 3000 rows in sample, 538311 estimated total rows >> >> (I suppose that means autovacuum is working.) Is this normal, or have >> I got >> something wrong? Why so many dead rows when just doing inserts? It is not >> that I think the number is too high, considering the number of rows in >> the >> table at the point where I copied this line. It is just that I do not >> understand why there are any. >> >> > Did you rollback some transactions? It will generate dead rows too - at > least I think so. > No, and the statistics confirm this. stock=> SELECT * FROM pg_stat_database WHERE datname = 'stock'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit -------+---------+-------------+-------------+---------------+-----------+----------- 16402 | stock | 1 | 1267 | 0 | 232234 | 146426135 (1 row) stock=> SELECT * FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname; relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del -------+------------+----------+----------+--------------+----------+---------------+-----------+-----------+----------- 89000 | public | co_name | 7 | 215873 | 1 | 30839 | 0 | 0 | 0 89004 | public | company | 9 | 219519 | 5624483 | 5648873 | 0 | 0 | 0 89008 | public | div | 7 | 0 | 1 | 0 | 0 | 0 | 0 89010 | public | djia | 4 | 2044 | 0 | 0 | 0 | 0 | 0 89012 | public | earn | 2 | 0 | 0 | 0 | 0 | 0 | 0 89014 | public | ibd | 5 | 0 | 1 | 0 | 0 | 0 | 0 89016 | public | merg | 2 | 0 | 0 | 0 | 0 | 0 | 0 89018 | public | price | 9 | 0 | 1 | 0 | 0 | 0 | 0 89022 | public | source | 3 | 27 | 0 | 0 | 0 | 0 | 0 89025 | public | sp_500 | 2 | 0 | 0 | 0 | 0 | 0 | 0 89027 | public | split | 3 | 0 | 1 | 0 | 0 | 0 | 0 89029 | public | tick | 13 | 400946 | 980983 | 1510922 | 0 | 0 | 0 89034 | public | vl_as | 7 | 6524595 | 1 | 932085 | 932085 | 0 | 0 89036 | public | vl_cf | 7 | 6317808 | 1 | 902544 | 902544 | 0 | 0 89038 | public | vl_in | 7 | 6798351 | 1 | 971193 | 966989 | 0 | 0 89040 | public | vl_li | 7 | 6524595 | 1 | 932085 | 932085 | 0 | 0 89042 | public | vl_mi | 7 | 6368579 | 1 | 909797 | 909797 | 0 | 0 89044 | public | vl_ranks | 8 | 7624818 | 1 | 985548 | 980982 | 0 | 0 -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jersey http://counter.li.org ^^-^^ 16:05:01 up 22 days, 9:23, 0 users, load average: 4.45, 4.11, 4.03