Possible to use first CTE as filter for second CTE?
Looking into my crystal ball, your query might work faster by orders of magnitude like this:

SELECT i.*, rr.urllist
FROM   vw_image i
LEFT   JOIN LATERAL (
   SELECT string_agg(r.url, ',') AS urllist
   FROM   resourceonpage rop
   JOIN   resource r ON r.id = rop.pageid
   WHERE  rop.siteid   = 2294  -- or: = i.siteid
   AND    r.resourceid = i.resourceid
   ) rr ON true
WHERE  i.siteid = 2294
AND    i.childtype = 'i'
ORDER  BY i.id
OFFSET 1 ROWS
FETCH  NEXT 50 ROWS ONLY;  -- or just: OFFSET 1 LIMIT 50


You don't need CTEs here, those will only slow you down.

I use a LATERAL join to use indexes on resourceonpage and resource and aggregate in the subquery right away. About LEFT JOIN LATERAL ... ON true:


What is the difference between LATERAL and a subquery in PostgreSQL?


Looking even deeper into my crystal ball, the best indexes might be:

CREATE INDEX ON vw_image (siteid, id) WHERE childtype = 'i';
CREATE INDEX ON resourceonpage (siteid, pageid);
CREATE INDEX ON resource (resourceid, id, url);


Appending url is only useful if you get index-only scans out of it.

An alternative approach would be to use parentheses in the FROM list like demonstrated here:


How to include missing data for multiple groupings within the time span?


But, according to my crystal ball (and an educated guess) the LATERAL version will be faster. There is not enough information in the question to give you more than educated guesses.