PostgreSQL is not using index when passed by param

Question

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

Laurenz Albe · Accepted Answer · 2022-08-12 18:10:27Z

2

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.

edited Aug 12, 2022 at 18:10

answered Jun 16, 2021 at 7:17

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

damike Over a year ago

Thx a lot - with RETURN QUERY EXECUTE ... USING it works!!

Erwin Brandstetter Over a year ago

I think this should read "PostgreSQL does not use the index" instead of "PostgreSQL cannot use the index", because Postgres absolutely can. It's just that for a generic value, the best generic plan may be not to use an index, where it would make sense to use one, if we had known the value (and column statistics derived from it). Another question linking here got mislead by "cannot".

Collectives™ on Stack Overflow

PostgreSQL is not using index when passed by param

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related