Question :
Is a scalar subquery that’s independent on parent query (does not refer to the parent table) evaluated only once, or for each row, regardless?
SELECT /* ... */
FROM
t1
WHERE
parent = (
-- is this sq evaluated once?
SELECT
t2.id
FROM t2
WHERE t2.id = 10
)
or using a CTE
WITH parent_id AS (
SELECT
t2.id
FROM t2
WHERE t2.id = 10
)
SELECT /* ... */
WHERE
parent = (
SELECT p.id FROM parent_id p -- is this sq evaluated once?
)
If the answer is that the subquery/CTE is evaluated for each row, would joining the CTE on each row improve the performance?
WITH parent_id AS (
SELECT
t2.id
FROM t2
WHERE t2.id = 10
)
SELECT /* ... */
FROM
t1, parent_id
WHERE
parent = parent_id.id
Also, is there a way to figure out from EXPLAIN ANALYZE
how many times an expression was evaluated?
Note: the examples are arbitrary and do not reflect real scenario, thus ignore the fact it doesn’t make sense to do a subquery to find out an id
which is already known (10
).
Answer :
It is evaluated once.
EXPLAIN ANALYZE
will display lines like these:
...
InitPlan 1 (returns $0)
-> Index Scan using t2_pkey on t2 (cost=0.29..5.31 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1)
Index Cond: (id = 10)
...
loops=1
tells you the subplan has only been evaluated once. The manual:
In some query plans, it is possible for a subplan node to be executed
more than once. For example, the inner index scan will be executed
once per outer row in the above nested-loop plan. In such cases, the
loops
value reports the total number of executions of the node, […]
With a correlated subquery like:
SELECT *, (SELECT id FROM t2 WHERE id = t1.parent_id) AS t2_id
FROM t1;
(or something more useful) you would see loops=
n
, where n
is the number of rows t1
.