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!

2
  • 1
    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 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). 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.

0

you can also use cte's:

    with definitely_better as (SELECT id FROM account WHERE login_id = ${loginId}) 

    SELECT t.id, t.date, t.amount, t.description FROM transaction t
    INNER JOIN definitely better ac ON t.account = ac.id
    WHERE t.processed = false AND t.updated_by_user = false
    AND t.category = 'uncategorized' ;
1
  • CTEs might behave differently than the OP's queries, depending on version of Postgres May 12 at 22:27

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.