This is a question about short-circuiting expensive JOINs or sub-queries in Postgresql (9.5 or 9.6). I'm also be interested in hearing how people in general solve the check-then-execute problem.
I'm writing a lot of queries that should only conditionally return a result, such as if the (web) user owns the record or if the record was modified. I'm trying to prevent building expensive views inside Postgresql and multiple back-and-forth queries to check for conditions in the application itself, so I attempt to write queries that first select the correct record and show which conditions failed, and only execute the view if the conditions pass.
For instance, this checks if the (application) user owns a record before returning it:
SELECT is_owner, is_newer, json
FROM (
SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
, modified >= created "is_newer"
FROM datasets
WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN LATERAL (
SELECT id
FROM datasets
WHERE is_owner and is_newer
) authed
ON cond.id = authed.id
LEFT JOIN LATERAL (
SELECT json
FROM view_dataset
WHERE id = authed.id
) dataset
ON true;
Resulting in (is owner):
is_owner | is_newer | json
t t {...}
And a negative result (not owner):
is_owner | is_newer | json
f t NULL
So the application knows which error to return but we don't have to build or parse the view if the conditions don't pass.
However, EXPLAIN ANALYSE shows Postgresql still executes the view query in the last LEFT LATERAL JOIN even though the middle JOIN doesn't have any results, and I can't get it to short-circuit to prevent the (expensive) view_dataset SELECT from running. If I set json
to null
the query skips everything but the first SELECT; but if it is set to a value from the last query, it will always execute all SELECTs, so I guess the query planner thinks it has to get a result for that json
field in the top SELECT query and doesn't short-circuit the JOINs.
I wonder if I can force Postgresql to drop the expensive view query.
I have also tried a CTE, which does seem to skip the JOIN query:
WITH cond as (
SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner", modified >= created "is_newer" FROM datasets WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
)
SELECT cond.id, cond.is_owner, cond.is_newer, json FROM
(SELECT id FROM cond WHERE cond.is_owner and cond.is_newer) filtered
LEFT JOIN LATERAL
(SELECT id, json from view_dataset) dataset
USING (id)
RIGHT JOIN cond
USING(id);
... but this query and variations are at least 2x slower.
So my question is how to maximise performance by short-circuiting JOINs or subqueries based on conditions; and I'm also interested to hear if somebody has other ideas how to implement some check-first-then-execute pattern such as checking for record ownership.
) dataset ON true;
to) dataset ON is_owner;
?