UPDATE и DELETE запросы сканируют все партиции, вместо одной нужной

Question

UPDATE и DELETE запросы сканируют все партиции, вместо одной нужной

ohmycto opened this issue 7 years ago · 16 comments

Problem description

Есть таблица visitors с колонкой account_id.

\d visitors
Таблица "visitors"
          Столбец           |             Тип             |                     Модификаторы
----------------------------+-----------------------------+-------------------------------------------------------
 id                         | integer                     | NOT NULL DEFAULT nextval('visitors_id_seq'::regclass)
 account_id                 | integer                     | NOT NULL

 ...

 Триггеры:
    search_columns_update BEFORE INSERT OR UPDATE ON visitors FOR EACH ROW EXECUTE PROCEDURE visitors_search_trigger()
Дочерних таблиц: 100 (чтобы просмотреть и их, воспользуйтесь \d+)

Таблица была разбита на 100 партиций по хэш-функции от account_id следующим образом:

SELECT create_hash_partitions('visitors', 'account_id', 100, FALSE);
SELECT partition_table_concurrently('visitors', 10000, 1.0);

Всё разбилось, данные разложились. Пример дочерней таблицы:

\d visitors_80
                                          Таблица "visitors_80"
          Столбец           |             Тип             |                     Модификаторы
----------------------------+-----------------------------+-------------------------------------------------------
 id                         | integer                     | NOT NULL DEFAULT nextval('visitors_id_seq'::regclass)
 account_id                 | integer                     | NOT NULL

 ...

 Ограничения-проверки:
    "pathman_visitors_80_check" CHECK (get_hash_part_idx(hashint4(account_id), 100) = 80)
Наследует: visitors

Проблема

При UPDATE и DELETE запросах, не смотря на явное указание ключа партиции, планировщик просматривает все 100 партиций, например:

explain analyze update visitors set updated_at = now() where id = 1 and account_id = 1;
                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Update on visitors  (cost=0.57..258.81 rows=101 width=2750) (actual time=77.315..77.315 rows=0 loops=1)
   Update on visitors
   Update on visitors_0
   ...
   Update on visitors_99
   ...
 Planning time: 24.816 ms
 Execution time: 86.232 ms
(407 строк)

explain analyze delete from visitors where id = 1 and account_id = 1;
                                                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on visitors  (cost=0.57..258.30 rows=101 width=6) (actual time=10.997..10.997 rows=0 loops=1)
   Delete on visitors
   Delete on visitors_0
   ...
   Delete on visitors_99
 Planning time: 23.559 ms
 Execution time: 11.652 ms
(407 строк)

При этом SELECT-запросы работают нормально:

explain analyze select 1 from visitors where id = 1 and account_id = 1;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.57..5.85 rows=2 width=4) (actual time=1.541..1.541 rows=0 loops=1)
   ->  Append  (cost=0.57..5.83 rows=2 width=0) (actual time=1.541..1.541 rows=0 loops=1)
         ->  Index Scan using visitors_pkey on visitors  (cost=0.57..2.99 rows=1 width=0) (actual time=0.959..0.959 rows=0 loops=1)
               Index Cond: (id = 1)
               Filter: (account_id = 1)
         ->  Index Scan using visitors_70_pkey on visitors_70  (cost=0.43..2.85 rows=1 width=0) (actual time=0.581..0.581 rows=0 loops=1)
               Index Cond: (id = 1)
               Filter: (account_id = 1)
 Planning time: 0.554 ms
 Execution time: 1.568 ms
(10 строк)

Environment

SELECT * FROM pg_extension;
    extname    | extowner | extnamespace | extrelocatable | extversion |      extconfig      | extcondition
---------------+----------+--------------+----------------+------------+---------------------+--------------
 plpgsql       |       10 |           11 | f              | 1.0        |                     |
 btree_gin     |       10 |         2200 | t              | 1.0        |                     |
 dblink        |       10 |        17225 | t              | 1.1        |                     |
 fuzzystrmatch |       10 |        17225 | t              | 1.0        |                     |
 intarray      |       10 |         2200 | t              | 1.0        |                     |
 postgres_fdw  |    16384 |         2200 | t              | 1.0        |                     |
 pgstattuple   |       10 |         2200 | t              | 1.1        |                     |
 pg_trgm       |       10 |         2200 | t              | 1.3        |                     |
 btree_gist    |    16384 |        17225 | t              | 1.2        |                     |
 hstore        |       10 |         2200 | t              | 1.4        |                     |
 pg_pathman    |    16384 |        17225 | f              | 1.4        | {46492080,46492091} | {"",""}
 pg_repack     |       10 |         2200 | f              | 1.4.2      |                     |
(12 строк)

SELECT version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 строка)

SELECT get_pathman_lib_version();
 get_pathman_lib_version
-------------------------
 1.4.12
(1 строка)

Answer 1 · 2019-04-12T11:08:44.000Z

@secoint сделайте вызов функции select set_enable_parent('visitors', false). После partition pruning должен заработать

Answer 2 · 2019-04-12T11:13:12.000Z

@maksm90 нет, это не помогает.

Answer 3 · 2019-04-12T11:15:49.000Z

@maksm90 нет, это не помогает.

Покажите вывод select * from pathman_config_params where partrel = 'visitors'::regclass

Answer 4 · 2019-04-12T11:17:57.000Z

select * from pathman_config_params where partrel = 'visitors'::regclass;
 partrel  | enable_parent | auto | init_callback | spawn_using_bgw
----------+---------------+------+---------------+-----------------
 visitors | f             | t    |               | f
(1 строка)

Answer 5 · 2019-04-12T11:20:38.000Z

select * from pathman_config_params where partrel = 'visitors'::regclass;
 partrel  | enable_parent | auto | init_callback | spawn_using_bgw
----------+---------------+------+---------------+-----------------
 visitors | f             | t    |               | f
(1 строка)

А теперь вывод:
explain update visitors set updated_at = now() where id = 1 and account_id = 1
explain delete from visitors where id = 1 and account_id = 1
и ещё
table pathman_concurrent_part_tasks

Answer 6 · 2019-04-12T11:24:38.000Z

explain update visitors set updated_at = now() where id = 1 and account_id = 1
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Update on visitors  (cost=0.57..258.79 rows=101 width=2750)
   Update on visitors
   Update on visitors_0
   Update on visitors_1
   Update on visitors_2
...
   Update on visitors_99
   ->  Index Scan using visitors_pkey on visitors  (cost=0.57..2.99 rows=1 width=2936)
         Index Cond: (id = 1)
         Filter: (account_id = 1)
   ->  Index Scan using visitors_0_account_id_idx on visitors_0  (cost=0.42..2.45 rows=1 width=3121)
         Index Cond: (account_id = 1)
         Filter: (id = 1)
...

(405 строк)

explain delete from visitors where id = 1 and account_id = 1

-- совершенно аналогично update

table pathman_concurrent_part_tasks;
 userid | pid | dbid | relid | processed | status
--------+-----+------+-------+-----------+--------
(0 строк)

Answer 7 · 2019-04-12T11:38:43.000Z

Хм, у меня ваш кейс не воспроизводится. Может проблема в триггере? Он у вас поставлен только на родительскую таблицу?

Answer 8 · 2019-04-12T11:41:26.000Z

Нет, все таблицы имеют триггеры:

\d+ visitors_80
...
Ограничения-проверки:
    "pathman_visitors_80_check" CHECK (get_hash_part_idx(hashint4(account_id), 100) = 80)
Наследует: visitors

Вы ранее дали совет обновиться, потом убрали его. Это не поможет?

Answer 9 · 2019-04-12T11:44:19.000Z

Хм, у меня ваш кейс не воспроизводится. Может проблема в триггере? Он у вас поставлен только на родительскую таблицу?

Прошу прощения, вы же про триггер, а не про ограничения... да, триггер только на родительской:

\d visitors
...
Триггеры:
    search_columns_update BEFORE INSERT OR UPDATE ON visitors FOR EACH ROW EXECUTE PROCEDURE visitors_search_trigger()
Дочерних таблиц: 100 (чтобы просмотреть и их, воспользуйтесь \d+)

На дочерних триггеров нет.

Answer 10 · 2019-04-12T11:49:37.000Z

Вы ранее дали совет обновиться, потом убрали его. Это не поможет?

На вашей версии pg_pathman у меня partition pruning тоже cрабатывает

триггер только на родительской

Походу, проблема в нём. Мне нужно тогда подольше времени, чтобы воспроизвести ваш кейс.

Answer 11 · 2019-04-12T11:58:15.000Z

На staging-сервере на тестовой базе обновили pg_pathman 1.4.12 => 1.4.13 и это сработало! UPDATE/DELETE ходят только в нужные партиции! Сейчас ещё поэкспериментирую и попробуем в основной базе.

Answer 12 · 2019-04-12T12:15:44.000Z

К сожалению в продакшене обновление не помогло. Видимо там что-то с базой. Я написал на info@postgrespro.ru запрос о помощи.

Answer 13 · 2019-04-12T12:31:10.000Z

@maksm90

Походу, проблема в нём. Мне нужно тогда подольше времени, чтобы воспроизвести ваш кейс.

Может ли быть такое, что работает не правильно из-за того, что процесс VACUUM ANALYZE convead.visitors; ещё не завершился? Таблица была порядка 150 Gb и он идёт уже 4+ часа после разбиения на партиции.

Answer 14 · 2019-04-12T12:46:25.000Z

Может ли быть такое, что работает не правильно из-за того, что процесс VACUUM ANALYZE convead.visitors; ещё не завершился? Таблица была порядка 150 Gb и он идёт уже 4+ часа после разбиения на партиции.

Возможно, надо смотреть

Answer 15 · 2019-04-12T15:15:28.000Z

VACUUM прошёл, ситуация в целом немного изменилась: теперь команда select set_enable_parent('visitors', false) действительно меняет поведение и планировщик начинает смотреть только в нужную партицию, но когда я это делаю, начинают сыпаться совершенно безобидные запросы с ошибкой variable not found in subplan target list. Приходится возвращать настройку обратно. Но в любом случае даже с включенной родительской таблицей должно не так ведь работать, должна быть лишь +1 строка в плане.

Answer 16 · 2019-04-12T15:30:48.000Z

VACUUM прошёл, ситуация в целом немного изменилась: теперь команда select set_enable_parent('visitors', false) действительно меняет поведение и планировщик начинает смотреть только в нужную партицию, но когда я это делаю, начинают сыпаться совершенно безобидные запросы с ошибкой variable not found in subplan target list. Приходится возвращать настройку обратно. Но в любом случае даже с включенной родительской таблицей должно не так ведь работать, должна быть лишь +1 строка в плане.

Согласен. Мы рассмотрим проблему partition pruning при включенном родителе. А заодно дополним partition_table_concurrently, чтобы родитель выключался после окончания разброса записей по секциям. И разберёмся с vacuum родительской таблицы и активностью родителя при нём.
Спасибо за кейс. @arssher FYI