Long planning time during autovacuum (to prevent wraparound)

[thamerlan]

Приветствую снова.
Понял из соседнего топика что pathman заморожен. Очень печально.
Всё же надеюсь на помощь по следующей проблеме.

Problem description

Если во время "autovacuum (to prevent wraparound)", бегущего на мастере, запустить select с фильтрацией по ключу секционирования (на реплике), то время планирования вырастает до неприличных размеров. То же самое происходит, если запустить vacuum freeze, но не происходит с просто vacuum. Причём вакуум может бежать даже на секции, которой нет в execution плане.

Пример.
План выполнения без vacuum:

Limit  (cost=0.57..1079.96 rows=1000 width=70) (actual time=0.132..10.285 rows=1000 loops=1)
   ->  Append  (cost=0.57..591016.70 rows=547545 width=70) (actual time=0.131..10.129 rows=1000 loops=1)
         ->  Index Scan Backward using rounds_history_77_started_at_idx on rounds_history_77 r_1  (cost=0.57..591010.10 rows=547544 width=70) (actual time=0.131..10.015 rows=1000 loops=1)
               Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
               Filter: (brand_id = 73)
               Rows Removed by Filter: 21651
         ->  Index Scan Backward using idx_rounds_history_started_at on rounds_history r  (cost=0.58..6.60 rows=1 width=70) (never executed)
               Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
               Filter: (brand_id = 73)
 Planning time: 0.723 ms
 Execution time: 10.405 ms

При бегущем autovacuum: rounds_history_79 (to prevent wraparound):

 Limit  (cost=0.57..1079.96 rows=1000 width=70) (actual time=10.475..412.404 rows=1000 loops=1)
   ->  Append  (cost=0.57..591016.70 rows=547545 width=70) (actual time=10.473..412.106 rows=1000 loops=1)
         ->  Index Scan Backward using rounds_history_77_started_at_idx on rounds_history_77 r_1  (cost=0.57..591010.10 rows=547544 width=70) (actual time=10.472..411.893 rows=1000 loops=1)
               Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
               Filter: (brand_id = 73)
               Rows Removed by Filter: 21651
         ->  Index Scan Backward using idx_rounds_history_started_at on rounds_history r  (cost=0.58..6.60 rows=1 width=70) (never executed)
               Index Cond: ((started_at > '2018-05-11 00:00:00'::timestamp without time zone) AND (started_at < '2018-05-11 11:01:00'::timestamp without time zone))
               Filter: (brand_id = 73)
 Planning time: 145640.316 ms
 Execution time: 412.652 ms

Секции разбиты по:
"pathman_rounds_history_77_check" CHECK (started_at >= '2018-05-07 00:00:00'::timestamp without time zone AND started_at < '2018-05-14 00:00:00'::timestamp without time zone)

Сам SQL:

SELECT * FROM rounds_history AS r
WHERE brand_id = 73 AND started_at>'2018-05-11 00:00:00' AND started_at<'2018-05-11 11:01:00'
ORDER BY started_at DESC
LIMIT 1000 OFFSET 0;

Есть ли какие-то идеи?

Environment

      extname       | extowner | extnamespace | extrelocatable | extversion |    extconfig    | extcondition 
--------------------+----------+--------------+----------------+------------+-----------------+--------------
 plpgsql            |       10 |           11 | f              | 1.0        |                 | 
 pg_stat_statements |       10 |         2200 | t              | 1.3        |                 | 
 postgres_fdw       |       10 |         2200 | t              | 1.0        |                 | 
 pgcrypto           |       10 |         2200 | t              | 1.2        |                 | 
 pg_cron            |       10 |         2200 | f              | 1.0        | {350179}        | {""}
 pg_pathman         |       10 |         2200 | f              | 1.4        | {350199,350210} | {"",""}
(6 rows)

PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

get_pathman_lib_version

10409
(1 row)

[funbringer]

Понял из соседнего топика что pathman заморожен. Очень печально.

На самом деле, мы по-прежнему стараемся оперативно отвечать на вопросы. Кроме того, мы собираемся выпустить мигратор pg_pathman <=> vanilla.

Причём вакуум может бежать даже на секции, которой нет в execution плане.

Я думаю, это может быть связано с какими-то блокировками. Пробовали смотреть pg_locks?

[thamerlan]

Блокировки, отобранные по pid'у:

Первые секунд 50 такое:
                     obj_name                      | obj_type | procpid |  locktype  |      mode       | granted | page | tuple | transactionid | virtualtransaction 
---------------------------------------------------+----------+---------+------------+-----------------+---------+------+-------+---------------+--------------------
 rounds_history                                    | table    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_finish_at_br_gc_pl             | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_gc_br_start_at                 | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_start_at_br_pl_gc              | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_pl_br_start_at                 | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_brand_id_ins_at_finished_false | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_brand_id_ins_at                | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_started_at                     | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_player_game                    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_brand_id_finished_at           | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_finished_at                    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 rounds_history_pkey                               | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
 idx_rounds_history_pl_br_finish_at                | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163665
                                                   |          |   17764 | virtualxid | ExclusiveLock   | t       |      |       |               | 16/7163665
(14 rows)

Потом ещё секунд 70 такое:

                       obj_name                       | obj_type | procpid |  locktype  |      mode       | granted | page | tuple | transactionid | virtualtransaction 
------------------------------------------------------+----------+---------+------------+-----------------+---------+------+-------+---------------+--------------------
 rounds_history_77                                    | table    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history                                       | table    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_pl_br_finish_at                   | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_finish_at_br_gc_pl                | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_gc_br_start_at                    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_start_at_br_pl_gc                 | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_pl_br_start_at                    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_brand_id_ins_at_finished_false    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_brand_id_ins_at                   | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_started_at                        | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_player_game                       | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_brand_id_finished_at              | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_finished_at                       | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_pkey                                  | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_finish_at_br_gc_pl             | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_77_pkey                               | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_77_finished_at_idx                    | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_gc_br_start_at                 | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_77_player_code_game_code_idx          | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_77_brand_id_finished_at_idx           | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_brand_id_ins_at                | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 rounds_history_77_started_at_idx                     | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_pl_br_finish_at                | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_pl_br_start_at                 | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_brand_id_ins_at_finished_false | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
 idx_rounds_history_77_start_at_br_pl_gc              | index    |   17764 | relation   | AccessShareLock | t       |      |       |               | 16/7163666
                                                      |          |   17764 | virtualxid | ExclusiveLock   | t       |      |       |               | 16/7163666
(27 rows)

Причём сейчас нету autovacuum ни на parent ни на children секции, но есть av wraparound на другие таблицы.