Why does Postgres not use the index when the comparison value is derived from my function?

Question

There is a table:

create table individual_profile_identity
(
    normalized_document_number varchar,
    normalized_name            varchar,
    birth_date                 date
);

There is an index in it:

create index individual_profile_identity_name_birth_dt_doc_idx
    on individual_profile_identity (
        normalized_name varchar_pattern_ops,
        birth_date,
        normalized_document_number
    );

There is a function that normalizes document_number and name fields:

create function rf_normalize_name(par_name character varying) returns character varying
    language plpgsql
as
$$
BEGIN
    RETURN nullif(upper(translate(par_name, 'A !@#$%^&*()_-+={}[]:;"''<>,.?/|\`~', 'A')), '0');
END
$$;

The query:

explain
select *
from individual_profile_identity
where normalized_name=rf_normalize_name('John Donne')
  and birth_date='2000-01-01'
  and normalized_document_number=rf_normalize_name('1234 567890');

The plan:

Index Scan using individual_profile_identity_name_birth_dt_doc_idx on individual_profile_identity  (cost=0.41..1935.18 rows=1 width=173)
  Index Cond: (birth_date = '2000-01-01'::date)
  Filter: (((normalized_name)::text = (rf_normalize_name('John Donne'::character varying))::text) AND ((normalized_document_number)::text = (rf_normalize_name('1234 567890'::character varying))::text))

As you can see, it uses the index but only with birth_date in it:

Index Cond: (birth_date = '2000-01-01'::date)

It completely doesn't make sense to me why postgres won't first run rf_normalize_name function and only then apply index to all 3 fields

Could anybody please explain the behaviour?

Not certain but try to mark your function as immutable or stable, otherwise it might have to be evaluated for every row — Bergi
– Bergi, Commented Nov 18, 2024 at 15:37
@Bergi, it works with either immutable or stable function, thank you very much! @frank-heikens thank you, will do it — Eduard Grigoryev
– Eduard Grigoryev, Commented Nov 18, 2024 at 15:42

Bergi · Accepted Answer · 2024-11-18 21:59:56Z

2

The CREATE FUNCTION statement by default creates a VOLATILE function if you don't specify otherwise. This "indicates that the function value can change even within a single table scan, so no optimizations can be made", forcing Postgres to call the function on every individual row and not being able to use the index.

You should

CREATE FUNCTION rf_normalize_name(par_name varchar) RETURNS varchar
    LANGUAGE SQL
    IMMUTABLE
    PARALLEL SAFE
    RETURN nullif(upper(translate(par_name, 'A !@#$%^&*()_-+={}[]:;"''<>,.?/|\`~', 'A')), '0');

then your query will use the index on all three columns.

answered Nov 18, 2024 at 21:59

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

1 Comment

Bergi Over a year ago

@EduardGrigoryev Thanks for your suggested edit, the SQL standard function body syntax is available since Postgres 14

Collectives™ on Stack Overflow

Why does Postgres not use the index when the comparison value is derived from my function?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related