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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-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 | Content-Type | Size |
---|---|---|
Analyze-fix.patch.gz | application/x-gzip | 2.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | mark | 2008-04-01 07:44:00 | Re: simple update queries take a long time - postgres 8.3.1 |
Previous Message | Tomasz Ostrowski | 2008-04-01 06:18:36 | Re: simple update queries take a long time - postgres 8.3.1 |
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2008-04-01 08:04:21 | Re: [HACKERS] ANALYZE getting dead tuple count hopelessly wrong |
Previous Message | Mathias Hasselmann | 2008-04-01 07:35:56 | Re: [HACKERS] Avahi support for Postgresql |