CREATE OR REPLACE FUNCTION fn_covering_works(wid INTEGER)
  RETURNS TABLE(work_id INTEGER)
AS
  $$
  WITH RECURSIVE func(work_id) AS
  (
    SELECT wid
    UNION ALL
    SELECT ad.adapted_id
    FROM func f JOIN adaptation ad ON f.work_id = ad.original_id
  )
  SELECT work_id
  FROM func
  $$
LANGUAGE 'sql' ROWS 1 COST 10000;

CREATE OR REPLACE VIEW covering_works_r AS
  SELECT
    w.id                    AS work_id,
    fn_covering_works(w.id) AS covering_work_id
  FROM work w;

-- This one is fine

EXPLAIN ANALYZE
SELECT
  w.id,
  cw.covering_work_id
FROM work w
  JOIN covering_works_r cw ON cw.work_id = w.id
WHERE w.id = 4249;

  id  | covering_work_id 
------+------------------
 4249 |             4249
 4249 |           102813
 4249 |             4250
 4249 |            23551
 4249 |            68931
 4249 |            74836
 4249 |            76088
 4249 |           111423
 4249 |           112399
 4249 |           112502
 4249 |           112666
 4249 |           120640
 4249 |           126994
 4249 |           133918
 4249 |           139519
 4249 |           142989
 4249 |           149393
 4249 |           111424

"Nested Loop  (cost=0.58..33.64 rows=1 width=8) (actual time=0.334..0.424 rows=18 loops=1)"
"  ->  Index Only Scan using work_pkey on work w  (cost=0.29..4.31 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=1)"
"        Index Cond: (id = 4249)"
"        Heap Fetches: 0"
"  ->  Index Only Scan using work_pkey on work w_1  (cost=0.29..29.31 rows=1 width=4) (actual time=0.309..0.393 rows=18 loops=1)"
"        Index Cond: (id = 4249)"
"        Heap Fetches: 0"
"Total runtime: 0.457 ms"

-- This one is too slow, but should be as fast as the first query.
-- At first sight it seems right, but the condition w_1.id=4249 (=w.id) isn't pushed to the second index scan.

EXPLAIN ANALYZE
SELECT
  w.id,
  cw.covering_work_id
FROM work w
  JOIN covering_works_r cw ON cw.work_id = w.id
WHERE w.first_release_id = 4249;

  id  | covering_work_id 
------+------------------
 4249 |             4249
 4249 |           102813
 4249 |             4250
 4249 |            23551
 4249 |            68931
 4249 |            74836
 4249 |            76088
 4249 |           111423
 4249 |           112399
 4249 |           112502
 4249 |           112666
 4249 |           120640
 4249 |           126994
 4249 |           133918
 4249 |           139519
 4249 |           142989
 4249 |           149393
 4249 |           111424

"Nested Loop  (cost=0.58..1659529.05 rows=1 width=8) (actual time=30.075..995.889 rows=18 loops=1)"
"  Join Filter: (w.id = w_1.id)"
"  Rows Removed by Join Filter: 81228"
"  ->  Index Scan using work_first_release_idx on work w  (cost=0.29..8.31 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=1)"
"        Index Cond: (first_release_id = 4249)"
"  ->  Index Only Scan using work_pkey on work w_1  (cost=0.29..1658030.07 rows=66252 width=4) (actual time=0.185..981.054 rows=81246 loops=1)"
"        Heap Fetches: 0"
"Total runtime: 995.916 ms"


# select id, first_release_id from work w where id = 4249;
  id  | first_release_id 
------+------------------
 4249 |             4249