Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong

From: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Stuart Brooks" <stuartb(at)cat(dot)co(dot)za>, pgsql-general(at)postgresql(dot)org, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong
Date: 2008-04-01 07:37:20
Message-ID: 2e78013d0804010037x2a7e16d0wbbc94e9e50ecf03e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
On Tue, Apr 1, 2008 at 1:22 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>
>  Please do --- I have a lot of other stuff on my plate.
>

Please see the attached patch. One change I made is to hold the SHARE lock
on the page while ANALYZE is reading tuples from it. I thought it would
be a right thing to do instead of repeatedly acquiring/releasing the lock.

Another thing I noticed while working on this is VACUUM probably reports the
number of dead tuples incorrectly. We don't count the DEAD line pointers as
"tups_vacuumed" which is fine if the line pointer was marked DEAD in the
immediately preceding heap_page_prune(). In that case the DEAD line pointer
is counted in "ndeleted" count returned by heap_page_prune(). But it fails to
count already DEAD line pointers.

For example

postgres=# CREATE TABLE test (a int, b char(500));
CREATE TABLE
postgres=# INSERT INTO test VALUES (generate_series(1,15),'foo');
INSERT 0 15
postgres=# DELETE FROM test;
DELETE 15
postgres=# select count(*) from test;
 count
-------
     0
(1 row)

postgres=# VACUUM VERBOSE test;
INFO:  vacuuming "public.test"
INFO:  "test": removed 0 row versions in 1 pages
INFO:  "test": found 0 removable, 0 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "test": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM


So VACUUM reports "zero" dead row versions which may seem
counter-intuitive especially in the autovac log message (as
someone may wonder why autovac got triggered on the table)

I am thinking we can make heap_page_prune() to only return
number of HOT tuples pruned and then explicitly count the DEAD
line pointers in tups_vacuumed.


Thanks,
Pavan

-- 
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

Attachment: Analyze-fix.patch.gz
Description: application/x-gzip (2.5 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-04-01 08:04:21
Subject: Re: [HACKERS] ANALYZE getting dead tuple counthopelessly wrong
Previous:From: Mathias HasselmannDate: 2008-04-01 07:35:56
Subject: Re: [HACKERS] Avahi support for Postgresql

pgsql-general by date

Next:From: markDate: 2008-04-01 07:44:00
Subject: Re: simple update queries take a long time - postgres 8.3.1
Previous:From: Tomasz OstrowskiDate: 2008-04-01 06:18:36
Subject: Re: simple update queries take a long time - postgres 8.3.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group