Re: [BUGS] BUG #14863: wrong reltuples statistics after vacuum without analyze

Поиск
Список
Период
Сортировка
От Pavel Suderevsky
Тема Re: [BUGS] BUG #14863: wrong reltuples statistics after vacuum without analyze
Дата
Msg-id CAEBTBzu5j_E1K1jb9OKwTZj98MPeM7V81-vadp5adRm=NhJnwA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [BUGS] BUG #14863: wrong reltuples statistics after vacuumwithout analyze  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Ответы Re: BUG #14863: wrong reltuples statistics after vacuum withoutanalyze
Список pgsql-bugs
This may or may not be the same issue Tomas Vondra reported recently; see
https://www.postgresql.org/message-id/94f58897-861b-accf-a9f1-af4be816c0d3@2ndquadrant.com
where he posted a patch to fix the problem.  Would you test that and see
if it solves the problem for you?

I've performed tests with and without patch provided by Tomas Vondra applied.
Conclusions: 
1. Patch fixed issue with adding n_dead_tup value to reltuples.
2. Even without dead_tuples in a table, vacuum without analyze would bring reltuples value to inconsistent state.
3. reltuples value increases with absolutely every lazy vacuum iteration.
4. Existence of dead tuples would just increase inaccuracy of reltuples because of higher vacuum operations rate (depends on autovacuum_naptime value) and higher iteration increment of reltuples overvaluing.
5. Bug is pretty critical, especially with big tables with high modification rate.

Now it has been tested on 9.6.2, 9.6.3 and 9.6.5. 

Please find postgresql96-vacuum-reltuples-fix-v2-tests.txt with performed tests attached. 


2017-10-20 2:58 GMT+03:00 Alvaro Herrera <alvherre@alvh.no-ip.org>:
psuderevsky@gmail.com wrote:

> I've faced strange behaviour of statistics state after (auto)VACUUM
> execution without ANALYZE. While ANALYZE operation makes statistics good,
> VACUUM breaks it to inconsistent state.

This may or may not be the same issue Tomas Vondra reported recently; see
https://www.postgresql.org/message-id/94f58897-861b-accf-a9f1-af4be816c0d3@2ndquadrant.com
where he posted a patch to fix the problem.  Would you test that and see
if it solves the problem for you?

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Вложения

В списке pgsql-bugs по дате отправления:

Предыдущее
От: YasonTR
Дата:
Сообщение: [BUGS] Possible regression in 'UPDATE ... SET () = ' with just one single column/row value since v10
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [BUGS] Possible regression in 'UPDATE ... SET () = ' with just one single column/row value since v10