pg_hint_plan с подзапросами

Как мы видели в предыдущих постах этой серии, вы определяете хинты с помощью псевдонимов таблиц. Но что делать, если у вас нет псевдонимов? Посмотрите в плане выполнения на его имя, которое может быть внутренним именем.

Пример:

drop table demo2, demo1;
create table demo1 ( id bigint primary key, a int, b int, d int);
create table demo2 ( id bigint primary key, a int, b int, d int);
insert into demo1 select generate_series(1,1000), 0, 0, 0 ;
vacuum analyze demo1;
vacuum analyze demo2;
insert into demo1 select generate_series(100000,200000), 0, 0, 0 ;
Войти в полноэкранный режим Выход из полноэкранного режима

Теперь у меня есть следующий запрос, который берет 1000 строк из таблицы demo1, чтобы переместить их в demo2:

explain (costs off, analyze)
WITH del AS(
 DELETE FROM demo1 a
 WHERE a.id in (SELECT id from demo1 my_batch limit 1000)
 RETURNING *
)  INSERT INTO demo2 
   SELECT  id, a, b c
 FROM del;
Войти в полноэкранный режим Выйти из полноэкранного режима

Вот план выполнения:

                                               QUERY PLAN

--------------------------------------------------------------------------------------------------------
 Insert on demo2 (actual time=18.627..18.635 rows=0 loops=1)
   CTE del
     ->  Delete on demo1 a (actual time=0.415..17.051 rows=1000 loops=1)
           ->  Hash Semi Join (actual time=0.399..16.534 rows=1000 loops=1)
                 Hash Cond: (a.id = "ANY_subquery".id)
                 ->  Seq Scan on demo1 a (actual time=0.006..10.032 rows=101001 loops=1)
                 ->  Hash (actual time=0.355..0.360 rows=1000 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 79kB
                       ->  Subquery Scan on "ANY_subquery" (actual time=0.006..0.256 rows=1000 loops=1)
                             ->  Limit (actual time=0.003..0.131 rows=1000 loops=1)
                                   ->  Seq Scan on demo1 my_batch (actual time=0.002..0.089 rows=1000 loops=1)
   ->  CTE Scan on del (actual time=0.417..17.282 rows=1000 loops=1)
 Planning Time: 0.247 ms
 Execution Time: 18.702 ms
(14 rows)


postgres=#
Войти в полноэкранный режим Выйти из полноэкранного режима

Это не очень эффективно, потому что demo1 полностью сканируется первой, в качестве таблицы-зонда для хэш-соединения. По сути, PostgreSQL хэширует 1000 id, которые я хочу удалить, но затем считывает всю таблицу, чтобы найти их. Вы можете подумать: ваша статистика не точна, попробуйте запустить ANALYZE. Хорошо, но моя цель — удалить много строк (партией по 1000). Я не хочу выполнять ANALYZE до и после. Вот где помогает pg_hint_plan. По дизайну моего запроса (с LIMIT 1000), я хочу получить вложенный цикл из этих строк, чтобы получить предсказуемую производительность (время зависит от количества обрабатываемых строк, а не от размера таблицы).

Вы можете подумать, что соединение происходит между demo1 a, таблицей для удаления, и demo1 my_batch, 1000 ids, но если вы попытаетесь использовать подсказки типа /*+ Leading( (my_batch a) ) NestLoop(my_batch a) */ вы не добьетесь успеха.

Посмотрите на план выполнения. Hash Join находится между двумя следующими сканированиями: Seq Scan on demo1 a и Subquery Scan on "ANY_subquery". Это дает мне псевдонимы, которые я должен использовать для порядка присоединения, направления и метаданных: a и "ANY_subquery".

Если я хочу использовать вложенный цикл, я могу добавить подсказку NestLoop("ANY_subquery" a), но это просто говорит, что соединение между двумя таблицами должно быть вложенным циклическим соединением. Порядок псевдонимов в этой подсказке не имеет значения. Нам нужен Leading hind с парой join: Leading( ("ANY_subquery" a) ).

explain (costs off, analyze)
/*+ Leading( ("ANY_subquery" a) ) NestLoop("ANY_subquery" a) */
WITH del AS(
 DELETE FROM demo1 a
 WHERE a.id in (SELECT id from demo1 my_batch limit 1000)
 RETURNING *
)  INSERT INTO demo2 
   SELECT  id, a, b c
 FROM del;
Вход в полноэкранный режим Выход из полноэкранного режима

Это тот план, который я хотел:

                                                  QUERY PLAN

---------------------------------------------------------------------------------------------------------------
 Insert on demo2 (actual time=4.267..4.268 rows=0 loops=1)
   CTE del
     ->  Delete on demo1 a (actual time=0.521..2.219 rows=1000 loops=1)
           ->  Nested Loop (actual time=0.508..1.701 rows=1000 loops=1)
                 ->  HashAggregate (actual time=0.492..0.641 rows=1000 loops=1)
                       Group Key: "ANY_subquery".id
                       Batches: 1  Memory Usage: 193kB
                       ->  Subquery Scan on "ANY_subquery" (actual time=0.069..0.305 rows=1000 loops=1)
                             ->  Limit (actual time=0.065..0.177 rows=1000 loops=1)
                                   ->  Seq Scan on demo1 my_batch (actual time=0.064..0.134 rows=1000 loops=1)
                 ->  Index Scan using demo1_pkey on demo1 a (actual time=0.001..0.001 rows=1 loops=1000)
                       Index Cond: (id = "ANY_subquery".id)
   ->  CTE Scan on del (actual time=0.523..2.526 rows=1000 loops=1)
 Planning Time: 0.231 ms
 Execution Time: 4.362 ms
(15 rows)
Вход в полноэкранный режим Выход из полноэкранного режима

Думаете, я счастлив, потому что это быстрее, 4,362 мс против 18,702 мс? Вовсе нет. Меня не волнуют эти миллисекунды. Что заставляет меня предпочесть этот план, так это масштабируемость: здесь ни одна операция не зависит от размера исходной таблицы. Благодаря моей подсказке я гарантирую сложность O(1) без риска перейти на сложность O(N) из-за устаревшей статистики.

Этот пример работает на PostgreSQL (с расширениями pg_hint_plan) или совместимом (например, YugabyteDB, где pg_hint_plan установлен по умолчанию)

Оцените статью
devanswers.ru
Добавить комментарий