explain analyze with Y as ( with X as ( select C.sha1 as cnt, 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' ) select X.cnt as cnt, D_R.dir as dir, X.loc as cd_loc, unnest(D_R.revs) as rev, unnest(D_R.locs) as dr_loc from D_R inner join X on (X.dir=D_R.dir) ) select Y.cnt as cnt, R.sha1 as rev, CL.path as path, DL.path as prefix from Y inner join revision as R on (R.id=Y.rev) inner join location as CL on (CL.id=Y.cd_loc) inner join location as DL on (DL.id=Y.dr_loc) ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=2.59..2638.22 rows=100 width=144) (actual time=0.110..2.971 rows=151 loops=1) -> Nested Loop (cost=2.15..1793.22 rows=100 width=101) (actual time=0.101..2.095 r ows=151 loops=1) -> Nested Loop (cost=1.72..948.22 rows=100 width=58) (actual time=0.087..1.240 rows=151 loops=1) -> ProjectSet (cost=1.29..102.22 rows=100 width=53) (actual time=0.072..0.252 rows=151 loops=1) -> Nested Loop (cost=1.29..101.61 rows=10 width=357) (actual time=0.069..0.152 rows=9 loops=1) -> ProjectSet (cost=0.86..16.97 rows=10 width=37) (actual time=0.052..0.063 rows=9 loops=1) -> Nested Loop (cost=0.86..16.91 rows=1 width=486) (actual time=0.046..0.050 rows=1 loops=1) -> Index Scan using content_sha1_key on content c (cost=0.43..8.45 rows=1 width=29) (actual time=0.026..0.028 rows=1 loops=1) 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.012..0.013 rows=1 loops=1) Index Cond: (cnt = c.id) -> Index Scan using d_r_dir_idx on d_r (cost=0.43..8.45 rows=1 width=336) (actual time=0.007..0.008 rows=1 loops=9) Index Cond: (dir = (unnest(c_d.dirs))) -> Index Scan using revision_pkey on revision r (cost=0.43..8.45 rows=1 width=29) (actual time=0.005..0.005 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.005..0.005 rows=1 loops=151) Index Cond: (id = (unnest(c_d.locs))) -> Index Scan using location_pkey on location dl (cost=0.43..8.45 rows=1 width=59) (actual time=0.004..0.004 rows=1 loops=151) Index Cond: (id = (unnest(d_r.locs))) Planning Time: 1.189 ms Execution Time: 3.104 ms