(An Untitled Masterwork)
ActivePublic
Actions

Authored by douardda on Jun 8 2021, 3:54 PM.

Tags

None

Subscribers

None

	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

Event Timeline

FTR:

fullset=# \d C_R
                     Table « public.c_r »
 Colonne |   Type   | Collationnement | NULL-able | Par défaut 
---------+----------+-----------------+-----------+------------
 cnt     | bigint   |                 | not null  | 
 revs    | bigint[] |                 |           | 
 locs    | bigint[] |                 |           | 
Index :
    "c_r_cnt_idx" btree (cnt)

fullset=# \d C_D
                     Table « public.c_d »
 Colonne |   Type   | Collationnement | NULL-able | Par défaut 
---------+----------+-----------------+-----------+------------
 cnt     | bigint   |                 | not null  | 
 dirs    | bigint[] |                 |           | 
 locs    | bigint[] |                 |           | 
Index :
    "c_d_cnt_idx" btree (cnt)

fullset=# \d D_R
                     Table « public.d_r »
 Colonne |   Type   | Collationnement | NULL-able | Par défaut 
---------+----------+-----------------+-----------+------------
 dir     | bigint   |                 | not null  | 
 revs    | bigint[] |                 |           | 
 locs    | bigint[] |                 |           | 
Index :
    "d_r_dir_idx" btree (dir)

and:

fullset=# \d revision
                                        Table « public.revision »
 Colonne |           Type           | Collationnement | NULL-able |              Par défaut              
---------+--------------------------+-----------------+-----------+--------------------------------------
 id      | bigint                   |                 | not null  | nextval('revision_id_seq'::regclass)
 sha1    | sha1_git                 |                 | not null  | 
 date    | timestamp with time zone |                 | not null  | 
 org     | bigint                   |                 |           | 
Index :
    "revision_pkey" PRIMARY KEY, btree (id)
    "revision_sha1_key" UNIQUE CONSTRAINT, btree (sha1)

fullset=# \d content
                                        Table « public.content »
 Colonne |           Type           | Collationnement | NULL-able |             Par défaut              
---------+--------------------------+-----------------+-----------+-------------------------------------
 id      | bigint                   |                 | not null  | nextval('content_id_seq'::regclass)
 sha1    | sha1_git                 |                 | not null  | 
 date    | timestamp with time zone |                 | not null  | 
Index :
    "content_pkey" PRIMARY KEY, btree (id)
    "content_sha1_key" UNIQUE CONSTRAINT, btree (sha1)


fullset=# \d directory
                                        Table « public.directory »
 Colonne |           Type           | Collationnement | NULL-able |              Par défaut               
---------+--------------------------+-----------------+-----------+---------------------------------------
 id      | bigint                   |                 | not null  | nextval('directory_id_seq'::regclass)
 sha1    | sha1_git                 |                 | not null  | 
 date    | timestamp with time zone |                 | not null  | 
Index :
    "directory_pkey" PRIMARY KEY, btree (id)
    "directory_sha1_key" UNIQUE CONSTRAINT, btree (sha1)

(An Untitled Masterwork)ActivePublicActions

Event Timeline

(An Untitled Masterwork)
ActivePublic
Actions