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
