为什么这个 postgresql text_pattern_ops 索引不在函数体内部使用?

Question

我用这段代码创建并填充了一个 Postgres 9.6 表

create table text_table(id serial primary key , value text);
create index on text_table(lower(value) text_pattern_ops);
insert into text_table(value)
select md5(random()::text)
from generate_series(0, 1000000);
create or replace function search_text_table(term text) returns table(id int) as $$
begin
return query (select text_table.id from text_table where lower(value) like term);
end;
$$ language plpgsql;
-- Query 1
explain analyze select t.id from text_table t where lower(t.value) like 'aba%';
-- Query 2
explain analyze select id from search_text_table('aba%');

在第一个查询中，value上的索引用于加快查询速度：

Bitmap Heap Scan on text_table t  (cost=216.95..8600.17 rows=5500 width=4) (actual time=0.162..0.798 rows=250 loops=1)
Filter: (lower(value) ~~ 'aba%'::text)
->  Bitmap Index Scan on text_table_lower_idx  (cost=0.00..215.57 rows=5500 width=0) (actual time=0.094..0.094 rows=250 loops=1)
Index Cond: ((lower(value) ~>=~ 'aba'::text) AND (lower(value) ~<~ 'abb'::text))
Total runtime: 0.833 ms

但是，当相同的代码作为search_text_table函数的一部分执行时，我假设不使用索引，因为查询的运行时间要多三个数量级：

Function Scan on search_text_table  (cost=0.25..10.25 rows=1000 width=4) (actual time=985.031..992.106 rows=68625 loops=1)
Total runtime: 994.515 ms

当提供给like运算符的术语是函数参数而不是常量字符串时，为什么 Postgres 不使用索引？

Answer 1

我无法重现这一点，但我怀疑您做了类似的事情：

CREATE TABLE text_table(
id serial PRIMARY KEY,
value text
);
CREATE INDEX ON text_table(lower(value) text_pattern_ops);
INSERT INTO text_table(value)
SELECT md5(random()::text)
FROM generate_series(0, 1000000);
CREATE FUNCTION search_text_table(term text)
RETURNS TABLE(id int) AS
$$BEGIN
RETURN QUERY (SELECT text_table.id
FROM text_table
WHERE lower(value) LIKE term);
END;$$
LANGUAGE plpgsql;
-- repeat a query like this 5 times
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('%abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('%abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('%abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('%abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('%abc%');
-- then run a query that could use the index
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');

让我们看看PostgreSQL到底做了什么：

-- requires being superuser
LOAD 'auto_explain';
SET log_min_messages = panic;
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = on;
SET client_min_messages = log;
SET auto_explain.log_analyze = on;
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
LOG:  duration: 2033.747 ms  plan:
Query Text: (select text_table.id from text_table where lower(value) like term)
Seq Scan on text_table  (cost=0.00..23334.01 rows=5000 width=4) (actual time=4.374..2033.395 rows=246 loops=1)
Filter: (lower(value) ~~ $1)
Rows Removed by Filter: 999755
LOG:  duration: 2034.259 ms  plan:
Query Text: explain analyze select id from search_text_table('abc%');
Function Scan on search_text_table  (cost=0.25..10.25 rows=1000 width=4) (actual time=2034.209..2034.240 rows=246 loops=1)
QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
Function Scan on search_text_table  (cost=0.25..10.25 rows=1000 width=4) (actual time=2034.209..2034.240 rows=246 loops=1)
Planning time: 0.194 ms
Execution time: 2034.353 ms
(3 rows)

在前 5 次执行中，PostgreSQL 将使用自定义计划，即它将使用实际参数值为语句创建一个计划。

在第六次执行时，它会检查在前 5 次执行中选择的计划是否优于通用计划，即不知道参数值的计划。我精心设计了我的示例，所以它不是，所以PostgreSQL决定从现在开始使用通用计划。

这意味着无论参数如何，它都将使用顺序扫描。您可以在EXPLAIN (ANALYZE, BUFFERS)输出中看到通用计划 — 请注意$1。

---

如果你像你展示的那样做实验，就会发生不同的事情。

终止 PostgreSQL 连接并启动一个新连接，以便 PostgreSQL 将丢失其所有缓存的查询计划。

然后像这样重试：

EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
-- requires being superuser
LOAD 'auto_explain';
SET log_min_messages = panic;
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_nested_statements = on;
SET client_min_messages = log;
SET auto_explain.log_analyze = on;
EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
LOG:  duration: 5.123 ms  plan:
Query Text: (select text_table.id from text_table where lower(value) like term)
Bitmap Heap Scan on text_table  (cost=4.62..70.57 rows=100 width=4) (actual time=0.272..4.889 rows=246 loops=1)
Filter: (lower(value) ~~ 'abc%'::text)
Heap Blocks: exact=242
->  Bitmap Index Scan on text_table_lower_idx  (cost=0.00..4.59 rows=17 width=0) (actual time=0.184..0.184 rows=246 loops=1)
Index Cond: ((lower(value) ~>=~ 'abc'::text) AND (lower(value) ~<~ 'abd'::text))
LOG:  duration: 6.289 ms  plan:
Query Text: EXPLAIN (ANALYZE) SELECT id FROM search_text_table('abc%');
Function Scan on search_text_table  (cost=0.25..10.25 rows=1000 width=4) (actual time=6.220..6.264 rows=246 loops=1)
QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
Function Scan on search_text_table  (cost=0.25..10.25 rows=1000 width=4) (actual time=6.220..6.264 rows=246 loops=1)
Planning time: 0.055 ms
Execution time: 6.398 ms
(3 rows)

这一次，前 5 次执行期间的自定义计划比具有顺序扫描的通用计划更好，因此 PostgreSQL 继续对后续执行使用自定义计划。

---

从 PostgreSQL v12 开始，您将能够使用参数plan_cache_mode来控制此行为。