Index isn't used when value is a result of a subquery or from a wrapper query I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts

id: uuid
...more irrelevant fields

events

id: serial
type: enum ['activated', 'deactivated', 'removed', ...more]
account_id: uuid
created_at: creation date of the row

events table index:
create index &quot;IDX_d82e2f903c778153e7781f455e&quot; on bank_account_events (type, bank_account_id);

I have a simple query which determines when the last event of a type occurred for a specific account:
explain select created_at
from bank_account_events
where bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'
  and type in ('activated', 'deactivated', 'removed')
order by id desc
limit 1;

The query's result shows that we use the correct index is being used:
Limit  (cost=339.51..339.51 rows=1 width=16)
  -&gt;  Sort  (cost=339.51..339.74 rows=94 width=16)
        Sort Key: id DESC
&quot;        -&gt;  Index Scan using &quot;&quot;IDX_d82e2f903c778153e7781f455e&quot;&quot; on bank_account_events  (cost=0.56..339.04 rows=94 width=16)&quot;
&quot;              Index Cond: ((type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])) AND (bank_account_id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid))

However, when I &quot;wrap&quot; this query and use it as a subquery the index isn't being used:
explain select ba.id,
  (
    select created_at
    from bank_account_events
    where bank_account_id = ba.id
      and type in ('activated', 'deactivated', 'removed')
    order by id desc
    limit 1
  ) status_date
from bank_accounts ba
where ba.id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964';

The result of the query above is:
Index Only Scan using &quot;&quot;PK_5a7a02c20412299d198e097a8fe&quot;&quot; on bank_accounts ba  (cost=0.27..8.43 rows=1 width=24)&quot;
  Index Cond: (id = 'b12edcab-9ac5-4a09-a84c-475c8a73c964'::uuid)
  SubPlan 1
    -&gt;  Limit  (cost=0.43..4.14 rows=1 width=16)
&quot;          -&gt;  Index Scan Backward using &quot;&quot;PK_dddc8f2295ddc2561044644a05a&quot;&quot; on bank_account_events  (cost=0.43..264940.10 rows=71513 width=16)&quot;
&quot;                Filter: ((bank_account_id = ba.id) AND (type = ANY ('{activated,deactivated,removed}'::bank_account_events_type_enum[])))

As you can see, it's using now the PK of the table (PK_dddc8f2295ddc2561044644a05a) which is just the serial id. Then it filters the results. Since the table has a few million rows, this is painfully slow.
Why isn't Postgres using the index in this scenario? The only change was that instead of referencing the id directly, we refer to it as ba.id.
Change the order of the columns in the index to support the B-tree search for the bank_account_id and filtering by type. Once filtered, all records are read ordered by id. By including the created_at column last, you have all the data the query needs in the index and it won't access the table at all.
CREATE INDEX &quot;nc_bank_account_events_bank_account_id&quot;
ON bank_account_events (bank_account_id, type, id, created_at);