Putting filters in INNER JOIN instead of WHERE

Question

I'm analyzing some code I just inherited at work and I have a question:

This is a snippet of what I inherited:

    Select sum(a.claim) as total
    from claims c
    inner join claim_entries ce
    on c.id = ce.claim_id
    and ce.deleted = 0
    and ce.dos >='2012-03-01'
    where c.deleted = 0
    and c.document_type = 0

I rewrote the code so the ON clauses are in the WHERE clause:

    Select sum(a.claim) as total
    from claims c
    inner join claim_entries ce 
    on c.id = ce.claim_id       
    where c.deleted = 0
    and c.document_type = 0        
    and ce.deleted = 0
    and ce.dos >='2012-03-01'

The original code ran in 36 seconds, while my rewrite ran in 9 seconds. Is there a difference to putting filters in the JOIN clause for an INNER JOIN as opposed to in the WHERE? I've only done that when using LEFT JOINS and never saw it done for an INNER JOIN. I'm also not sure why the original would take 4x longer than the rewrite, unless the data is cached after I ran the original.

With INNER JOIN it shouldn't have any impact. I assume that second one is faster because it read data from buffer/cache. — Lukasz Szozda, Aug 22 '18 at 17:42
Possible duplicate of [Performance difference: condition placed at INNER JOIN vs WHERE clause](https://stackoverflow.com/questions/10848975/performance-difference-condition-placed-at-inner-join-vs-where-clause) — Mạnh Quyết Nguyễn, Aug 22 '18 at 17:42
That rewrite doesn't look quite right, since you've got the 'AND' above the 'ON'. Are you sure you didn't move the date compare down to the WHERE clause? Because I'm pretty sure that would speed your query substantially. I could be wrong, but I think that trying to match rows based on a 'greater than' is more expensive than just filtering them afterward. — SQLCliff, Aug 22 '18 at 17:45
We can speculate a lot about the reason why is faster, but it's simpler to get the execution plan of each one of those. Please get the plans and post them. — The Impaler, Aug 22 '18 at 17:53
Use: `explain select ...` on both queries and post the result. — The Impaler, Aug 22 '18 at 18:33
@TheImpaler: no, he/she should use `explain (analyze, buffers)` not just a simple explain — , Aug 22 '18 at 18:50
@jackstraw22: there is no need for a "plan button". Just run `explain (analyze, buffers) select ...` and post the results as [**formatted text**](http://stackoverflow.com/help/formatting), [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). If you don't know how to copy results as plain text from TOAD, use psql and copy and paste the result from the command line. — , Aug 22 '18 at 18:51
@a_horse_with_no_name Sure, that will help. I still think that PostgreSQL's weak point are the explain plan. Damn, they look ugly; can't really make out the difference between **access** predicates and **filter** predicates. That something they should learn from the Oracle team. — The Impaler, Aug 22 '18 at 18:52
@TheImpaler: I think they are much better than Oracle's output and a lot easier to read (and they don't do the stupid line wrapping in a single line if the step description is longer than 80 characters). It's a lot harder to get the equivalent to `explain (analyze, buffers)` in Oracle — , Aug 22 '18 at 18:53
@a_horse_with_no_name I hear you. It's just the first thing I look is the the "access" predicates vs the "filter" predicates. I [personally] find that difficult to read in PostgreSQL. — The Impaler, Aug 22 '18 at 18:57
It's right below node where the index usage is shown: `Index Cond:` == "access", `Filter:` == "filter", no need to scroll away from the plan to find that information — , Aug 22 '18 at 18:59
I figured out how to use Explain. Much different from SQL Server. It doesn't show any difference when moving the filters. But someone just explained that it can be faster putting the filters in the Inner Join because SQL runs the Joins before the Where. — jackstraw22, Aug 22 '18 at 19:16

score 3 · Answer 1 · answered Aug 23 '18 at 07:00

With an inner join, it doesn't matter if you put a condition in the ON clause or in the WHERE clause. The execution plan will be the same. Use EXPLAIN to confirm that.

The difference in the execution time is likely due to caching effects. Repeat the test several times to get reliable results.

Putting filters in INNER JOIN instead of WHERE

1 Answers1