Message-ID: <425EA496.2030501@archonet.com>
Date: Thu, 14 Apr 2005 18:12:54 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
MIME-Version: 1.0
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: pgsql-sql@postgresql.org
Subject: Re: row-attribute in EXPLAIN-output doesn't match count(*)
References: <200504141838.14705.andreak@officenet.no>
In-Reply-To: <200504141838.14705.andreak@officenet.no>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andreas Joseph Krogh wrote:
> Hi everybody, I have table which inly has 298 rows in it, but EXPLAIN ANALYZE 
> shows that PG thinks it has as much as 160057 rows AFAICT from the 
> EXPLAIN-output.
> 

>  I've just run a "VACUUM FULL VERBOSE" on the whole database, which gave the 
> following output on the relevant table(onp_web_index):
> 
> INFO:  vacuuming "public.onp_web_index"
> INFO:  "onp_web_index": found 0 removable, 160057 nonremovable row versions in 
> 206940 pages
> DETAIL:  159759 dead row versions cannot be removed yet.

A long-lived transaction can still see these row versions, so vacuum 
can't delete them. Until that transaction is committed/rolled back PG 
has to keep the old versions of those rows available, because it might 
be able to see them.

So - do you have an application/client that has had a single transaction 
open for a long time. A very long time unless you update this table a lot.

-- 
   Richard Huxton
   Archonet Ltd