Difference between the following postgresql indexes On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row.
The following 2 indexes, I would think will act the exact same way, but they don't:
1.
&quot;cleanupAndAlerted001&quot; btree (doneCleanup, alertedUser, doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;




&quot;cleanupAndAlerted002&quot; btree (doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

The query performed is of the form:
select distinct id, field1, field2, ... from my_table
WHERE
doneCleanup = true AND
alertedUser = true AND
doneCleanupTimestamp&lt;='2021...validtimestamp'
order by id asc, initialTimestamp asc limit 100 offset 50000;

Considering the conditions, I would expect identical behavior. Yet, the first index, even when the 2nd doesn't exist, does not get picked up.
Creating the 2nd index and doing the query again works, the new index gets used.
Am I assuming things I shouldn't be assuming? If so, what should I know about this behavior?
Edit: using postgres 11.2
Check for table and index sizes. Your first index uses 3 columns and its size is probably very big so optimizer chooses full table scan.
Index scan vs full table scan also depends on random_page_cost (default value 4).
If random_page_cost is set lower optimizer will chose index scans over full table scans.