Postgresql not using index for queries using CURRENT_USER

user_8389128 0
Viewed 322

I have a Postgresql table:

  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)
  );

There is a unique index on the user_name column. When I query the table using a constant, the index is used for the query:

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)

but when I use the current_user variable a sequential scan is performed instead of index scan:

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)

Tried casting:

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;

But still, a sequential scan is used, not really sure why I see this behavior, any way to make this query use an index scan?

Edit: Can anyone answer why inline casting from name datatype to varchar fails?

@jimjonesbr gave an answer on how to use an index scan using prepared statements. I speculate the problem is with the query planner not handling the datatype conversion properly.

I have tried to use a function instead of a prepared statement but was unable to run explain analyze to check index scan is performed. what I was able to notice is that runtime is faster when passing current_user as a varchar parameter instead of using it inline which might suggest index scan is used.

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;

and running with explain analyze produce this:

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

the time difference might suggest index scan is preform in get_acls(current_user::text)

query-optimization postgresql sql
1 Answers

I honestly don't understand this behaviour either - although there might be a good reason for that. I'll put down some thoughts and we can get the discussion started.

Perhaps it's the casting from name to text that does not work as we expect, no matter if the name parameter comes from a session information function or not. :

Test 1: casting CURRENT_USER to 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

Test 2: casting a name string to text in the WHERE clause:

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

Test 3: Casting from name to text in a CTE:

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

However, we can sort of process this casting prior to the query, so that the planer already sees the parameter as text. An alternative is to use a PREPARED STATEMENT (or a function as you suggested). In the following example we create a statement that has a text parameter, so that if there is a casting it should happen "before" the query runs:

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)

Demo: db<>fiddle

user_22753880
Related