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

Re: BUG #4812: statistics not available on tables containing only hstore column

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: George Su <george(dot)su(at)efrontier(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #4812: statistics not available on tables containing only hstore column
Date: 2009-05-18 11:46:41
Message-ID: 4A114AA1.2020300@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
George Su wrote:
> If I create a table containing only hstore columns, then no statistics on
> row count and page count is available even after analyzing.

(As a work-around, running VACUUM on the table will update those stats.)

Hmm, we intentionally skip analyze on tables that have no analyzable 
columns, but ISTM as well that relpages and reltuples in pg_class should 
still be updated. We have this in analyze.c:

	/*
	 * Quit if no analyzable columns
	 */
	if (attr_cnt <= 0 && !analyzableindex)
	{
		/*
		 * We report that the table is empty; this is just so that the
		 * autovacuum code doesn't go nuts trying to get stats about a
		 * zero-column table.
		 */
		if (update_reltuples)
			pgstat_report_analyze(onerel, 0, 0);
		goto cleanup;
	}

ISTM we should not skip the analyze if run as a stand-alone ANALYZE. I'm 
not sure what problem the author of that comment envisioned with 
autovacuum, but resetting the live and dead tuple counters doesn't seem 
right to me. In the worst case, autovacuum launches auto-analyze on the 
table whenever there has been enough update/delete activity, and you 
never reach the auto*vacuum* threshold because the dead tuple counter is 
always cleared.

Does anyone see a problem with the attached patch, changing the code 
above so that in a stand-alone ANALYZE (including auto-analyze), the 
table is analyzed to update relpages and reltuples even if there's no 
analyzable columns?

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Attachment: analyze-zerocol-tables-too-1.patch
Description: text/x-diff (1.1 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-05-18 15:46:22
Subject: Re: BUG #4812: statistics not available on tables containing only hstore column
Previous:From: Tom LaneDate: 2009-05-18 01:59:17
Subject: Re: BUG #4814: Segmentation fault when using indexed prefix FT search

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