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

ohmycto · 2019-04-12T09:43:25Z

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 строка)

The text was updated successfully, but these errors were encountered:

maksm90 · 2019-04-12T11:08:44Z

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

ohmycto · 2019-04-12T11:13:12Z

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

maksm90 · 2019-04-12T11:15:49Z

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

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

ohmycto · 2019-04-12T11:17:57Z

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

maksm90 · 2019-04-12T11:20:38Z

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

ohmycto · 2019-04-12T11:24:38Z

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 строк)

maksm90 · 2019-04-12T11:38:43Z

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

ohmycto · 2019-04-12T11:41:26Z

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

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

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

ohmycto · 2019-04-12T11:44:19Z

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

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

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

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

maksm90 · 2019-04-12T11:49:37Z

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

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

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

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

ohmycto · 2019-04-12T11:58:15Z

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

ohmycto · 2019-04-12T12:15:44Z

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

ohmycto · 2019-04-12T12:31:10Z

@maksm90

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

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

maksm90 · 2019-04-12T12:46:25Z

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

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

ohmycto · 2019-04-12T15:15:28Z

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

maksm90 · 2019-04-12T15:30:48Z

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

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019 •

edited

Loading

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019 •

edited

Loading

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

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

Comments

ohmycto commented Apr 12, 2019

Problem description

Проблема

Environment

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019

ohmycto commented Apr 12, 2019 • edited Loading

maksm90 commented Apr 12, 2019

ohmycto commented Apr 12, 2019

maksm90 commented Apr 12, 2019 • edited Loading

ohmycto commented Apr 12, 2019 •

edited

Loading

maksm90 commented Apr 12, 2019 •

edited

Loading