if a non-correlated subquery is repeated at several places in the query, can it be cached and the result reused?

postgresql, postgresql-9.1, sql, subquery

Solution

You can use a common table express (`WITH`)

with cte as 
(
     SELECT userid FROM groupmembers WHERE groupid = 65553
)
SELECT 
    date_trunc('day', assigndate)e,  
    count(CASE WHEN a.assigneeid = 65548 AND a.assigneeid IN  
           (SELECT userid from cte) then 1 else null end) assigned,
...

Problem

If I have a query like ``` SELECT date_trunc('day', assigndate)e, count(CASE WHEN a.assigneeid = 65548 AND a.assigneeid IN (SELECT userid FROM groupmembers WHERE groupid = 65553) THEN 1 ELSE NULL END) assigned, count(CASE WHEN a.assigneeid = 65548 AND a.completedtime IS NOT NULL AND a.assigneeid IN (SELECT userid FROM groupmembers WHERE groupid = 65553) THEN 1 ELSE NULL END) completed FROM ASSIGNMENT a WHERE assigndate > CURRENT_TIMESTAMP - interval '20 days' GROUP BY date_trunc('day',assigndate); ``` The subquery in question is ``` SELECT userid FROM groupmembers WHERE groupid = 65553 ``` then since the subquery is `not co-related` to the parent query, it will be executed just once and the cached result will be used. But since the subquery is present at 2 locations in the query, then according to the `SQL plan`, it is evaluated twice. Is there any way to `cache` the result of that subquery and use it at both the locations ? The subquery can't be converted to a join as is no single field on which to join (and it can't be an unconditional join, as the count will become wrong then)

Original source