PgSQL: присвоение значения столбца переменной делает параметр запроса несвязанным
При запуске кода ниже:
drop table if exists demo;
drop table if exists demo_test;
drop table if exists demo_result;
create table demo as select md5(v::text) from generate_series(1, 1000000) v;
create index on demo (md5 text_pattern_ops);
analyze demo;
create table demo_test
as select left(md5(v::text), 5) || '%' as "patt" from generate_series(2000000, 2000010) v;
create table demo_result (row text);
load 'auto_explain';
set auto_explain.log_min_duration to 0;
set auto_explain.log_analyze to true;
set auto_explain.log_nested_statements to true;
do $$
declare
row record;
pattern text;
begin
for row in select patt from demo_test loop
pattern = row.patt; -- <--- CRUCIAL LINE
insert into demo_result select * from demo where md5 like pattern;
end loop;
end$$;
PostgreSQL генерирует следующий план запроса:
2017-10-02 17:03:48 CEST [18038-23] app=psql barczynski@barczynski LOG: duration: 0.021 ms plan:
Query Text: insert into demo_result select * from demo where md5 like pattern
Insert on demo_result (cost=0.42..8.45 rows=100 width=33) (actual time=0.021..0.021 rows=0 loops=1)
-> Index Only Scan using demo_md5_idx on demo (cost=0.42..8.45 rows=100 width=33) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: ((md5 ~>=~ '791cc'::text) AND (md5 ~<~ '791cd'::text))
Filter: (md5 ~~ '791cc%'::text)
Heap Fetches: 1
Но после удаления pattern
переменная и встраивание row.patt
в where
состояние:
insert into demo_result select * from demo where md5 like row.patt;
PostgreSQL обрабатывает параметр как связывание:
2017-10-02 17:03:02 CEST [17901-23] app=psql barczynski@barczynski LOG: duration: 89.636 ms plan:
Query Text: insert into demo_result select * from demo where md5 like row.patt
Insert on demo_result (cost=0.00..20834.00 rows=5000 width=33) (actual time=89.636..89.636 rows=0 loops=1)
-> Seq Scan on demo (cost=0.00..20834.00 rows=5000 width=33) (actual time=47.255..89.628 rows=1 loops=1)
Filter: (md5 ~~ $4)
Rows Removed by Filter: 999999
Я понимаю, что последний план использует последовательное сканирование, потому что PostgreSQL предполагает, что параметры связывания начинаются с подстановочных знаков.
У меня вопрос, почему дополнительное назначение включает и выключает параметр bind?
1 ответ
Разница заключается в данных, доступных оптимизатору во время просмотра запроса.
При первом запросе привязанный параметр доступен для просмотра оптимизатором. Таким образом, он видит, что подстановочных знаков нет, и знает, что индекс можно использовать.
insert into demo_result select * from demo where md5 like '791cc%';
Второй запрос не имеет представления о том, как будет выглядеть шаблон, поэтому он не может предположить, что индекс хорош.
Я подозреваю, что если бы у вас был шаблон с лидирующим подстановочным знаком "%791cc", вы бы увидели, что для обоих подходов используется один и тот же план запросов, поскольку seq_scan будет использоваться для обоих.