Postgresql在使用CURRENT_USER进行查询时未使用索引

Question

Postgresql在使用CURRENT_USER进行查询时未使用索引

5

我有一个PostgreSQL表:

  CREATE TABLE IF NOT EXISTS acls1k (
    pkey serial PRIMARY KEY,
    user_name VARCHAR(50),
    tenant_id VARCHAR(36),
    CONSTRAINT user_name_unique1k UNIQUE (user_name)
  );

用户名称列上有一个独特的索引。当我使用常量查询表时，该查询会使用索引：

explain analyze select * from acls1k where user_name = 'p1kuser1t1';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using user_name_unique1k on acls1k  (cost=0.28..8.29 rows=1 width=53) (actual time=0.071..0.073 rows=1 loops=1)
   Index Cond: ((user_name)::text = 'p1kuser1t1'::text)
 Planning Time: 0.240 ms
 Execution Time: 0.094 ms
(4 rows)

但是当我使用current_user变量时，会执行顺序扫描而不是索引扫描：

explain analyze select * from acls1k where user_name = current_user;
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on acls1k  (cost=0.00..59.00 rows=1 width=53) (actual time=0.162..0.845 rows=1 loops=1)
   Filter: ((user_name)::text = CURRENT_USER)
   Rows Removed by Filter: 999
 Planning Time: 0.097 ms
 Execution Time: 0.861 ms
(5 rows)

尝试强制类型转换：

explain analyze select * from acls1k where user_name = CAST(current_user as varchar(50));
explain analyze select * from acls1k where user_name = CAST(current_user as text);
explain analyze select * from acls1k where user_name = current_user::text;

不过，仍然使用顺序扫描，不确定为什么会出现这种情况，有没有办法使此查询使用索引扫描？

编辑： 有人能回答为什么从name数据类型到varchar的内联转换失败吗？

@jimjonesbr提供了使用prepared statements的索引扫描答案。我猜测问题在于查询规划器未正确处理数据类型转换。

我尝试使用函数而不是预处理语句，但无法运行解释分析来检查是否执行索引扫描。我能够注意到的是，在将current_user作为varchar参数传递而不是内联使用时，运行时速度更快，这可能表明使用了索引扫描。

create function get_acl(auser varchar(63))
returns varchar(36)
language plpgsql
as
$$
begin
        return (select tenant_id from acls1k where user_name = auser);
end;
$$;


create function get_acl_inline()
returns varchar(36)
language plpgsql
as
$$
begin
        return (select tenant_id from acls1k where user_name = current_user);
end;

使用explain analyze运行会产生以下结果:

explain analyze select from get_acl(current_user::text);
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Function Scan on get_acl  (cost=0.26..0.27 rows=1 width=0) (actual time=0.435..0.436 rows=1 loops=1)
 Planning Time: 0.027 ms
 Execution Time: 0.456 ms
(3 rows)

explain analyze select from get_acl_inline();
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Function Scan on get_acl_inline  (cost=0.25..0.26 rows=1 width=0) (actual time=1.833..1.834 rows=1 loops=1)
 Planning Time: 0.024 ms
 Execution Time: 1.850 ms

时间差异可能表明在 get_acls(current_user::text) 中执行了索引扫描。

- darc

如果不将current_user强制转换为text或varchar类型，则预计会进行序列扫描，因为current_user是name类型，其比较语义不同。使用强制转换应该可以解决问题，请确保在表格刚刚填充时进行了分析。 - Daniel Vérité

@DanielVérité 我还在努力理解这个问题。看起来强制转换是不够的：https://www.db-fiddle.com/f/8qX2tbtU3tNH1U6QsVTyaf/3 我错过了什么吗？ - Jim Jones

@JimJones：你的例子非常不同，因为它插入了100万次相同的用户，而darc在该列上有唯一约束。如果统计数据表明这个常量非常频繁（在这种情况下索引是适得其反的），Postgres可能不会使用索引来查找一个常量。 - Daniel Vérité

@DanielVérité 我明白，但我的观点是：为什么要使用预处理语句来使用索引，虽然这是一个坏主意？我的意思是，两个查询的数据都是相同的..我有点困惑，很抱歉我在打扰 :) - Jim Jones

1

简单的函数最好定义为 language sql stable - 这样你也可以看到完整的执行计划。 - user330315

显示剩余2条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jim Jones · Accepted Answer

我真的也不理解这种行为，尽管可能有很好的原因。我会列出一些想法，然后我们可以开始讨论。

也许是从name到text的转换并没有按照我们的预期工作，无论name参数是否来自会话信息函数。 测试1：将CURRENT_USER转换为text：

EXPLAIN (ANALYSE,COSTS OFF) 
SELECT * FROM acls1k WHERE user_name = CURRENT_USER::text; 

Gather (actual time=252.261..252.312 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on acls1k (actual time=234.139..234.140 rows=0 loops=3)
        Filter: (user_name = (CURRENT_USER)::text)
        Rows Removed by Filter: 333333
Planning Time: 0.262 ms
Execution Time: 252.328 ms

测试2: 将WHERE子句中的name字符串转换为文本:

EXPLAIN (ANALYSE,COSTS OFF) 
SELECT * FROM acls1k WHERE user_name = 'myuser'::name::text; 

Gather (actual time=200.262..200.321 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on acls1k (actual time=180.093..180.094 rows=0 loops=3)
        Filter: (user_name = 'myuser'::text COLLATE "C")
        Rows Removed by Filter: 333333
Planning Time: 0.043 ms
Execution Time: 200.334 ms

测试3: 在CTE中将name转换为text:

EXPLAIN (ANALYSE,COSTS OFF) 
WITH u (uname) AS (SELECT CURRENT_USER::text)
SELECT * FROM acls1k a 
JOIN u ON a.user_name = u.uname; 

Gather (actual time=229.228..229.280 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on acls1k a (actual time=208.065..208.066 rows=0 loops=3)
        Filter: (user_name = (CURRENT_USER)::text)
        Rows Removed by Filter: 333333
Planning Time: 0.085 ms
Execution Time: 229.293 ms

然而，在查询之前，我们可以对此进行一定的转换处理，使计划程序已将参数视为text。另一种选择是使用预处理语句（或您建议的函数）。在下面的示例中，我们创建一个带有text参数的语句，因此如果有转换，它应该发生在查询运行之前。

PREPARE pu (text) AS SELECT * FROM acls1k WHERE user_name = $1;

EXPLAIN (ANALYSE,COSTS OFF) 
EXECUTE pu(CURRENT_USER);

                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Scan using idx on acls1k (actual time=0.078..0.079 rows=0 loops=1)
   Index Cond: (user_name = 'myuser'::text)
 Planning Time: 10.607 ms
 Execution Time: 0.095 ms
(4 rows)

演示：db<>fiddle