Indexes for optimising SQL Joins in Postgres

Given the below query

SELECT * FROM A 
INNER JOIN B ON (A.b_id = B.id) 
WHERE (A.user_id = 'XXX' AND B.provider_id = 'XXX' AND A.type = 'PENDING') 
ORDER BY A.created_at DESC LIMIT 1;

The variable values in the query are A.user_id and B.provider_id, the type is always queried on 'PENDING'.

I am planning to add a compound + partial index on A A(user_id, created_at) where type = 'PENDING'

Also the number of records in A >> B.

Given A.user_id, B.provider_id, A.b_id all are foreign keys. Is there any way I can optimize the query?

Solution

Given that you are doing an inner join, I would first express the query as follows, with the join in the opposite direction:

SELECT *
FROM B
INNER JOIN A ON A.b_id = B.id
WHERE A.user_id = 'XXX' AND A.type = 'PENDING' AND
      B.provider_id = 'XXX'
ORDER BY
    A.created_at DESC
LIMIT 1;

Then I would add the following index to the A table:

CREATE INDEX idx_a ON A (user_id, type, created_at, b_id);

This four column index should cover the join from B to A, as well as the WHERE clause and also the ORDER BY sort at the end of the query. Note that we could probably also have left the query with the join order as you originally wrote above, and this index could still be used.