- Notifications
You must be signed in to change notification settings - Fork67
Description
Приветствую снова.
Понял из соседнего топика что 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 rWHERE 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 DESCLIMIT 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)