0

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN account ac ON t.account = ac.id AND ac.login_id = ${loginId}
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

Will this query perform faster with a subquery on accounts, for example:

SELECT t.id, t.date, t.amount, t.description FROM transaction t
INNER JOIN (SELECT id FROM account WHERE login_id = ${loginId}) ac ON t.account = ac.id
WHERE t.processed = false AND t.updated_by_user = false
AND t.category = 'uncategorized' ;

I'd appreciate some insightful comments on this, thanks in advance!

  • Why don't you check on your own system (with your disks, RAM and CPU) by running EXPLAIN on both? You could also do some timings – Vérace Jul 18 '16 at 5:52
  • 3
    I'm pretty sure Postgres' query optimizer is smart enough to use the same execution plan for both statements. You can use explain (analyze, verbose) select ... to verify that (but remember to run each statement several times to exclude the caching effects from the timing). – a_horse_with_no_name Jul 18 '16 at 6:18
0

Use the INNER JOIN whenever possible because it's easier to read. It otherwise should not make a difference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.