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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [GENERAL] ANALYZE getting dead tuple count hopelessly wrong
Date: 2008-04-02 23:04:09
Message-ID: 17953.1207177449@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
I wrote:
> ... What I propose therefore
> is combining this patch with my older one so that ANALYZE counts
> according to the following rules:

> REDIRECT line pointer:		ignore
> DEAD line pointer:		count as dead
> HEAPTUPLE_LIVE tuple:		count as live, include in statistics pool
> HEAPTUPLE_DEAD:			count as dead
> HEAPTUPLE_RECENTLY_DEAD:	count as dead
> HEAPTUPLE_INSERT_IN_PROGRESS:	ignore
> HEAPTUPLE_DELETE_IN_PROGRESS:	count as live

While working on this I realized that there's a special case ANALYZE has
to face that is not faced by VACUUM: it might see tuples inserted or
deleted by its own transaction.  For example consider

	begin;
	... load lots of data into mytable ...
	analyze mytable;
	... issue complex queries against mytable ...
	commit;

This is not an uncommon scenario, particularly with respect to temporary
tables.  ANALYZE's historical behavior of sampling everything that's
good according to SnapshotNow does the right thing here, but ignoring
INSERT_IN_PROGRESS tuples would not.

The right way seems to be to treat our own insertions as live during
ANALYZE, but then subtract off our own pending insertions from the
live-tuples count sent to the stats collector.  pgstat_report_analyze()
can handle the latter part by groveling through the backend's pending
statistics data.

Comments?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Decibel!Date: 2008-04-02 23:05:39
Subject: Re: writing a MIN(RECORD) aggregate
Previous:From: Bruce MomjianDate: 2008-04-02 23:03:09
Subject: Re: [GENERAL] SHA1 on postgres 8.3

pgsql-general by date

Next:From: Ron MayerDate: 2008-04-02 23:41:16
Subject: modules
Previous:From: Bruce MomjianDate: 2008-04-02 23:03:09
Subject: Re: [GENERAL] SHA1 on postgres 8.3

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