Hello all,
I'm trying to write queries with conditional JOINs. The goal is to guard expensive views by checking some conditions first and not execute the view at all if any of the conditions fail. Think along the lines of REST, like Not Authorized or Not Modified.
My problem is that the Postgresql (9.5 and 9.6) query planner executes LEFT and LATERAL JOINed queries even when the JOIN conditions are false.
I don't understand why.
I could work around some of this by adding a (dirty-ish?) CASE statement or a slower and more complex CTE construct or even create functions for each of these queries, but I'd really like to know why the most simple solution doesn't work. I tried many variations, but in my use cases, Postgresql always executes JOINs if the conditions are not constants.
What I'm really looking for is a general pattern with good performance to guard expensive views.
Here is an example query (is_owner is false):
SELECT is_owner, is_newer, json
FROM (
SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea17' "is_owner"
, modified >= created "is_newer"
FROM datasets
WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN view_dataset view
ON view.id = cond.id
AND cond.is_owner
AND cond.is_newer;
Which returns (correctly, but executing the slow JOINed view):
is_owner: false
is_newer: true
json: (null)
I don't want that query to execute the JOINed view, that is the whole point of those conditions, but it does.
Here is a working sql fiddle:
http://sqlfiddle.com/#!17/6882c6/5
And here is the stackoverflow thread:
https://dba.stackexchange.com/questions/211642/optimising-expensive-join-subquery-by-filtering-with-conditionals
(Note that in the fiddle example there is a 0.5 second delay in the view to simulate it being expensive, so it should be pretty clear if the query planner executes the view or not.)
Thanks for any insight!
