PostgreSQL is not using index when passed by param

user_444033 0
Viewed 123

Having a index:

CREATE INDEX foo_idx ON my_table(name, value) WHERE deleted_by_request_id IS NULL AND name IN ('a', 'b');

Tables properties:

SELECT COUNT(*) FROM my_table => 76.560.014
SELECT COUNT(*) FROM my_table WHERE deleted_by_request_id IS NULL AND name = 'a' => 3.190.001
SELECT COUNT(*) FROM my_table WHERE deleted_by_request_id IS NULL AND name = 'b' => 3.190.001

When running:

SELECT id FROM my_table WHERE deleted_by_request_id IS NULL AND name = 'a' AND value = 'bla'

it takes 14msec

Now i put the query in a function:

CREATE OR REPLACE FUNCTION my_func(param_value VARCHAR(4000))
  RETURNS TABLE (id INTEGER)
  LANGUAGE SQL
AS $$
  SELECT id FROM my_table WHERE deleted_by_request_id IS NULL AND name = 'a' AND value = param_value
$$;

Still 14msec

 CREATE OR REPLACE FUNCTION my_func(param_name VARCHAR(4000), param_value VARCHAR(4000))
  RETURNS TABLE (id INTEGER)
  LANGUAGE SQL
AS $$
  SELECT id FROM my_table WHERE deleted_by_request_id IS NULL AND name = param_name AND value = param_value
$$;

Calling it with SELECT my_func('a', 'bla')

30 seconds

Any ideas?

PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

postgresql
1 Answers

PostgreSQL does not use the index, because the parameter value is not known at query planning time.

You could use a dynamically generated SQL statement rather than parameters.

user_64643080
Related