2

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).

4

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.

  • 1
    yes, the manual quote explains why I'm seeing loops=2 in the Seq Scan on t2 in here (though it's scanning 4 rows... also seems to happen only for IN operator) – dwelle Apr 14 '18 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.