Search Postgresql Archives

Re: Seems like there is an issue with reltuples showing twice the number of rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> 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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux