explain analyze 
select R.sha1 as rev, R.date as date, X.sha1 as cnt, DL.path as prefix, CL.path as path
from (
  select C.sha1, unnest(dirs) as dir, unnest(locs) as loc from C_D
  inner join content as C on (C.id=C_D.cnt)
  where C.sha1='\xf178ba3c4b2684a9331a5b6080b3f6ecbba583f0') as X
inner join (select dir, unnest(revs) as rev, unnest(locs) as loc from D_R) as Y ON (Y.dir = X.dir)
inner join revision as R on (R.id=Y.rev)
inner join location as CL on (CL.id=X.loc)
inner join location as DL on (DL.id=Y.loc)
;

                                                                                 QUERY PLAN                                                                                  
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1018.49..377686.18 rows=10 width=152) (actual time=10.454..23243.537 rows=151 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Nested Loop  (cost=18.49..376685.18 rows=4 width=152) (actual time=530.851..23220.796 rows=50 loops=3)
         ->  Nested Loop  (cost=18.06..376683.31 rows=4 width=109) (actual time=530.846..23220.735 rows=50 loops=3)
               ->  Nested Loop  (cost=17.62..376649.51 rows=4 width=66) (actual time=530.832..23220.661 rows=50 loops=3)
                     ->  Hash Join  (cost=17.19..376647.63 rows=4 width=45) (actual time=530.811..23220.517 rows=50 loops=3)
                           Hash Cond: (d_r.dir = (unnest(c_d.dirs)))
                           ->  ProjectSet  (cost=0.00..131469.77 rows=7429110 width=24) (actual time=10.587..15017.045 rows=196777821 loops=3)
                                 ->  Parallel Seq Scan on d_r  (cost=0.00..86895.11 rows=742911 width=336) (actual time=0.011..80.319 rows=594329 loops=3)
                           ->  Hash  (cost=17.07..17.07 rows=10 width=37) (actual time=0.055..0.056 rows=9 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 ->  ProjectSet  (cost=0.86..16.97 rows=10 width=37) (actual time=0.049..0.052 rows=9 loops=3)
                                       ->  Nested Loop  (cost=0.86..16.91 rows=1 width=486) (actual time=0.047..0.048 rows=1 loops=3)
                                             ->  Index Scan using content_sha1_key on content c  (cost=0.43..8.45 rows=1 width=29) (actual time=0.024..0.024 rows=1 loops=3)
                                                   Index Cond: ((sha1)::bytea = '\xf178ba3c4b2684a9331a5b6080b3f6ecbba583f0'::bytea)
                                             ->  Index Scan using c_d_cnt_idx on c_d  (cost=0.43..8.45 rows=1 width=473) (actual time=0.019..0.020 rows=1 loops=3)
                                                   Index Cond: (cnt = c.id)
                     ->  Index Scan using revision_pkey on revision r  (cost=0.43..0.47 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=151)
                           Index Cond: (id = (unnest(d_r.revs)))
               ->  Index Scan using location_pkey on location cl  (cost=0.43..8.45 rows=1 width=59) (actual time=0.001..0.001 rows=1 loops=151)
                     Index Cond: (id = (unnest(c_d.locs)))
         ->  Index Scan using location_pkey on location dl  (cost=0.43..0.47 rows=1 width=59) (actual time=0.001..0.001 rows=1 loops=151)
               Index Cond: (id = (unnest(d_r.locs)))
 Planning Time: 1.157 ms
 JIT:
   Functions: 114
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 8.208 ms, Inlining 0.000 ms, Optimization 1.247 ms, Emission 29.976 ms, Total 39.431 ms
 Execution Time: 23248.495 ms
(30 lignes)