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 будет использоваться для обоих.

Другие вопросы по тегам