Indexes for optimising SQL Joins in Postgres

Question

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?

Tim Biegeleisen · Accepted Answer · 2021-11-09 05:24:22Z

1

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.

edited Nov 9, 2021 at 5:24

answered Nov 9, 2021 at 4:27

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

7 Comments

Laurenz Albe Over a year ago

If you are driving at a nested loop join, you should also index b(id). The columns are in the wrong order: b_id should be last or not there at all.

user330315 Over a year ago

The join order won't change a thing in terms of the execution plan or performance

Dinesh Gowda Over a year ago

Can i use a partial index on 'PENDING' since i know I will be only quering on that. CREATE INDEX idx_a ON A (user_id, created_at, b_id) where type = 'PENDING';

Tim Biegeleisen Over a year ago

Try it out and then check the execution plan.

Dinesh Gowda Over a year ago

It does improve the performance slightly, I think when the data size is large the difference can be seen since the index size will be less in case of partial index.

|

Collectives™ on Stack Overflow

Indexes for optimising SQL Joins in Postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related