From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> |
Cc: | Ranjith Ramachandra <ranjith(at)mammoth(dot)io>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Seems like there is an issue with reltuples showing twice the number of rows |
Date: | 2018-04-03 14:19:23 |
Message-ID: | 3272.1522765163@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> On 04/03/2018 11:14 AM, Ranjith Ramachandra wrote:
>> it returns
>> reltuples | n_live_tup | n_dead_tup
>> -------------+------------+------------
>> 2.7209e+06 | 1360448 | 1360448
>>
>> If I run analyze main_csv_0f247511f5c247c8813ba3cec90c6ae1_yf34fbb38d
>> and I run the same query again,
>> reltuples | n_live_tup | n_dead_tup
>> -------------+------------+------------
>> 1.36045e+06 | 1360448 | 1360448
>>
>> But after some time the value goes back to being double the value.
> There was a difference between VACUUM and ANALYZE in handling recently
> dead rows (essentially deleted rows that can't be removed yet), causing
> similar changes to reltuples. Essentially if you do VACUUM and ANALYZE,
> it may set reltuples to rather different estimates. That is fixed now
> and should be in the next minor release.
No, I think this is the *other* thing we fixed recently: VACUUM thinks
it should set reltuples to total tuples (live + dead) whereas ANALYZE
counts only live tuples. We did not risk back-patching that.
The question I'd ask about this case is why is there persistently 100%
bloat? Those dead tuples should've gotten reclaimed by autovacuum.
Perhaps an open prepared transaction, or some such?
> It's probably better to use n_live_tup instead, though. I'd say that's
> closer to the "live tuples" definition.
Yeah, you might be better off looking at that, particularly since it
updates on-the-fly not just after a vacuum or analyze.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | hmidi slim | 2018-04-03 14:35:35 | Re: How to get an inclusive interval when using daterange |
Previous Message | Adrian Klaver | 2018-04-03 13:48:51 | Re: How to install pgTAP on cenos machine |