具有参数的PostgreSQL函数未使用现有索引

Question

我有一个PostgreSQL plpgsql函数，并注意到它没有使用MATERIALIZED VIEW(my_view)上的现有索引，而是在查询。

这个函数的特殊之处在于，它调用另一个函数(check_user)传递其参数v_username。结果是一个布尔值，并且取决于函数通过大小写构造决定的值，如果用户从给定的目标视图 (my_view) 返回所有数据，或者从另一个表连接的视图中获取所有数据。

CREATE OR REPLACE function my_view_secured (v_username text)
RETURNS setof my_view
LANGUAGE plpgsql stable  
AS 
$function$
declare
v_show_all boolean := check_user(v_username);
begin

CASE 
WHEN v_show_all then return query select * from my_view;
WHEN v_show_all = false then return query select st.* from my_view st join other_table st2 on st2.id = st.id;
end case;
end;
$function$
;

当直接/不使用函数执行 CASE-WHEN 部分中定义的两个查询时，PostgreSQL 正在使用现有索引，并且查询返回数据的速度非常快(50 毫秒)。 调用此包装器函数 (my_view_secured) 时，我假设不使用索引，因为返回大约需要 10-20 秒。

select * from my_view--需要一些毫秒

与

select * from my_view_secured('RETURNS_TRUE')-- 需要 10-20 秒，尽管基础查询与上述查询相同

(也许是我的 DBeaver 设置错误地给我一种印象，即查询 1 只需要一些毫秒)

我在这里读到PostgreSQL在通过参数传递时没有使用index，这是因为由于函数参数，PostgreSQL无法优化查询。我的第一次尝试是将这种 PLPGSQL 风格的函数重写为纯 SQL 本机查询，但我很难找到正确的逻辑。

从客户端的角度来看，我只想调用该函数

SELECT .. FROM my_view_secured ('someusername')

该函数负责要返回的数据(全部或联接，具体取决于函数调用check_user返回值)。索引本身是在连接使用的 id 字段上设置的。

有没有人有想法，如何解决这个问题？

一些附加信息：

PGSQL-Version: 13.6
my_view (MV): 19 million rows (index on id col)
other_table: 60k rows (unique index on id col)

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1;
SET auto_explain.log_nested_statements = ON;
SET client_min_messages TO log;

1. 直接查询MV：

explain (analyze, verbose, buffers)  select * from my_view
Seq Scan on my_view  (cost=0.00..598633.52 rows=18902952 width=185) (actual time=0.807..15754.467 rows=18902952 loops=1)
Output: <removed>
Buffers: shared read=409604
Planning:
Buffers: shared hit=67 read=8
Planning Time: 2.870 ms
Execution Time: 16662.400 ms

2a) 通过包装函数查询 MV(返回所有数据/无连接)：

explain (analyze, verbose, buffers)  select * from my_view_secured ('some_username_returning_all_data') 
Function Scan on my_view_secured  (cost=0.25..10.25 rows=1000 width=3462) (actual time=9006.965..11887.518 rows=18902952 loops=1)
Output: <removed>
Function Call: my_view_secured('some_username_returning_to_all_data'::text)
Buffers: shared hit=174 read=409590, temp read=353030 written=353030
Planning Time: 0.052 ms
Execution Time: 13091.509 ms

2b) 通过包装函数查询 MV(返回连接数据)：

explain (analyze, verbose, buffers)  select * from my_view_secured ('some_username_triggering_join') 
Function Scan on my_view_secured  (cost=0.25..10.25 rows=1000 width=3462) (actual time=10183.590..11756.417 rows=8624367 loops=1)
Output: <removed>
Function Call: my_view_secured('some_username_triggering_join'::text)
Buffers: shared hit=126 read=409792, temp read=161138 written=161138
Planning Time: 0.050 ms
Execution Time: 12434.169 ms

Answer 1

我刚刚重新创建了您的方案，并按预期获得了嵌套查询的索引扫描。Postgres 绝对可以在这里使用索引。

PL/pgSQL 处理嵌套的 SQL DML 语句，如下所示：控制到达的每个语句都被解析、计划和执行。由于两个SELECT语句都不涉及任何参数，因此这些计划将立即保存并在重复执行时重用。无论哪种方式，如果普通select * from my_view;"使用索引"，则嵌套语句的情况应该完全相同。

一定有什么事情没有反映在你的问题中。

几点注意事项：

• 您误解了链接的答案。您的情况有所不同，因为两个查询都不涉及参数。

• 大约。。。

  不使用视图 (my_view) 上的现有索引，而是查询。

  也许只是措辞模棱两可，但要明确：视图上没有索引。表格(包括MATERIALIZED VIEWs) 可以有索引。VIEW基本上只是一个存储的查询，其中包含一些附加的辅助设置，附加到具有重写规则的空临时表。可以对基础表编制索引。

• 您如何知道嵌套查询一开始就"不使用现有索引">？检查此类嵌套语句的查询计划并非易事。看：

  • 获取嵌套在 PL/pgSQL 函数中的 SQL 语句的查询计划

看来你吠错了树。