How does Postgres choos which index to use in case if multiple indexes are present?

Question

I am new to Postgres and a bit confused on how Postgres decides which index to use if I have more than one btree indexes defined as below.

CREATE INDEX index_1 ON sample_table USING btree (col1, col2, COALESCE(col3, 'col3'::text));

CREATE INDEX index_2 ON sample_table USING btree (col1, COALESCE(col3, 'col3'::text));

I am using col1, col2, COALESCE(col3, 'col3'::text) in my join condition when I write to sample_table (from source tables) but when I do a explain analyze to get the query plan I see sometimes that it uses index_2 to scan rather than index_1 and sometimes just goes with sequential scan .I want to understand what can make Postgres to use one index over another?

Answer 1

Without seeing EXPLAIN (ANALYZE, BUFFERS) output, I can only give a generic answer.

PostgreSQL considers all execution plans that are feasible and estimates the row count and cost for each node. Then it takes the plan with the lowest cost estimate.

It could be that the condition on col2 is sometimes more selective and sometimes less, for example because you sometimes compare it to rare and sometimes to frequent values. If the condition involving col2 is not selective, it does not matzer much which of the two indexes is used. In that case PostgreSQL prefers the smaller two-column index.