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 (