Reuse subquery as in another result

Question

In a typical social-network-like application I have the following query that gathers news from a set of followed channels.
The main query gets the news, while the subquery gets the followed channels:

QUERY 1: Get latest news:

select n.id, n.title, n.description
from community.news n
where n.id_channel in (
       select id_channel from community.user_follows uf 
       where uf.id_user ='3d4c788e0e34657457febba2040297c7'
)

The app needs this table of recent news, but also needs the followed channels, so I execute the subquery again:

QUERY 2, same as the subquery:

select id_channel from community.user_follows uf 
where uf.id_user ='3d4c788e0e34657457febba2040297c7'

Can I optimize this? Both queries are always executed in sequence. Is it worth to use temporary tables or something like that? Or is there a way to access a subquery result?

Answer 1

Or is there a way to access a subquery result?

Sure there are ways, but only in the same query at the next higher level.
You can extend the scope with a CTE, but still limited to the same query (anywhere).
To preserve a derived table across queries in the same session, materialize the result in a temporary table.
Or a regular table (maybe UNLOGGED?) to extend the scope to all sessions.

What's reasonable depends on the details of your installation.

Ideally, you do it all in a single query, in a single query level, even. The difficulty is the return type. You have to combine columns, possibly creating some redundancy in the result. You can either reduce query time or reduce redundant data in the result, but not both. Combining everything into a single JSON column might be an alternative.

To get everything in a single query:

SELECT n.id, n.title, n.description, id_channel 
FROM   community.user_follows uf 
JOIN   community.news         n USING (id_channel)
WHERE  uf.id_user ='3d4c788e0e34657457febba2040297c7';