#	exclusive	inclusive	rows x	rows	loops	node
1.	1157.847	2804.350	↑ 1.3	880770	1	→ Hash Join (cost=111713.06..204500.51 rows=1145877 width=217) (actual time=188.185..2804.350 rows=880770 loops=1) Hash Cond: ((promotable_paths.owner)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
2.	1458.433	1458.433	↓ 1.2	880770	1	→ Seq Scan on asset_link_tree promotable_paths (cost=0.00..62246.62 rows=763282 width=85) (actual time=0.102..1458.433 rows=880770 loops=1) Filter: (((path)::text ~~ '/1/2/18/%'::text) AND ((owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,01000301,00010201,01010101
3.	0.049	188.070	↓ 5.5	33	1	→ Hash (cost=111712.99..111712.99 rows=6 width=164) (actual time=188.070..188.070 rows=33 loops=1)
4.	0.457	188.021	↓ 5.5	33	1	→ Nested Loop (cost=849.27..111712.99 rows=6 width=164) (actual time=61.831..188.021 rows=33 loops=1) Join Filter: ((sub_path_inactive_contributor_map.child_path_owner)::text = (base_plus_between_contexts.context)::text)
5.	0.307	56.851	↓ 33.0	33	1	→ Hash Join (cost=849.27..861.99 rows=1 width=100) (actual time=53.355..56.851 rows=33 loops=1) Hash Cond: ((moo.related_context)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
6.	0.369	49.971	↓ 4.0	800	1	→ Append (cost=574.54..586.50 rows=200 width=64) (actual time=46.776..49.971 rows=800 loops=1)
7.	2.137	49.601	↓ 4.0	799	1	→ GroupAggregate (cost=574.54..584.49 rows=199 width=64) (actual time=46.776..49.601 rows=799 loops=1)
8.	12.582	47.464	↓ 2.4	2368	1	→ Sort (cost=574.54..577.03 rows=995 width=64) (actual time=46.768..47.464 rows=2368 loops=1) Sort Key: moo.related_context Sort Method: quicksort Memory: 157kB
9.	2.130	34.882	↓ 2.4	2368	1	→ Subquery Scan moo (cost=0.00..525.00 rows=995 width=64) (actual time=4.619..34.882 rows=2368 loops=1) Filter: ((moo.related_context)::text <> (moo.subcontext)::text)
10.	32.752	32.752	↓ 3.2	3168	1	→ Function Scan on getrelatedcontexts (cost=0.00..512.50 rows=1000 width=32) (actual time=4.617..32.752 rows=3168 loops=1)
11.	0.001	0.001	↑ 1.0	1	1	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
12.	0.022	6.573	↓ 33.0	33	1	→ Hash (cost=274.71..274.71 rows=1 width=36) (actual time=6.573..6.573 rows=33 loops=1)
13.	0.028	6.551	↓ 33.0	33	1	→ Subquery Scan sub_path_inactive_contributor_map (cost=274.70..274.71 rows=1 width=36) (actual time=6.486..6.551 rows=33 loops=1)
14.	0.027	6.523	↓ 33.0	33	1	→ Unique (cost=274.70..274.70 rows=1 width=48) (actual time=6.484..6.523 rows=33 loops=1)
15.	0.108	6.496	↓ 33.0	33	1	→ Sort (cost=274.70..274.70 rows=1 width=48) (actual time=6.484..6.496 rows=33 loops=1) Sort Key: source_paths.child_path_owner, source_paths.source_path_owner Sort Method: quicksort Memory: 19kB
16.	2.248	6.388	↓ 33.0	33	1	→ Subquery Scan source_paths (cost=0.00..274.69 rows=1 width=48) (actual time=4.186..6.388 rows=33 loops=1) Filter: ((source_paths.child_path_owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,0 -> Index Scan using asset_link_tree_pkey on asset_link_tree (cost=0.00..274.63 rows=1 width=13) (actual time=4.168..4.560 rows= Index Cond: ((master_assetid = 18) AND ((path)::text = '/1/2/'::text)) Filter: ((owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,01000301,00010201,0
17.						SubPlan (forSubquery Scan source_paths)
18.	4.140	4.140	↑ 1.0	1	1	→ Aggregate (cost=265.00..265.01 rows=1 width=32) (actual time=4.140..4.140 rows=1 loops=1) -> Function Scan on gethighercontexts (cost=0.00..262.50 rows=1000 width=32) (actual time=3.452..3.745 rows=799 l
19.	0.561	130.713	↑ 36.4	33	33	→ Append (cost=0.00..110823.98 rows=1201 width=32) (actual time=3.906..3.961 rows=33 loops=33)
20.	0.132	128.964	↑ 200.0	1	33	→ Subquery Scan base_plus_between_contexts (cost=0.00..110551.96 rows=200 width=32) (actual time=3.905..3.908 rows=1 loops=33)
21.	0.066	4.026	↑ 200.0	1	33	→ Append (cost=0.00..547.46 rows=200 width=32) (actual time=0.120..0.122 rows=1 loops=33)
22.	0.000	3.894	↓ 0.0	0	33	→ Result (cost=0.00..547.44 rows=199 width=32) (actual time=0.118..0.118 rows=0 loops=33)
23.	0.267	3.894	↓ 0.0	0	33	→ HashSetOp Intersect (cost=0.00..547.44 rows=199 width=32) (actual time=0.118..0.118 rows=0 loops=33)
24.	0.381	3.627	↑ 2.5	800	1	→ Append (cost=0.00..542.45 rows=1995 width=32) (actual time=0.039..3.627 rows=800 loops=1)
25.	0.002	0.039	↑ 995.0	1	1	→ Subquery Scan "SELECT 1" (cost=0.00..272.45 rows=995 width=32) (actual time=0.038..0.039 rows=1 loops=1)
26.	0.037	0.037	↑ 995.0	1	1	→ Function Scan on getcontexts (cost=0.00..262.50 rows=995 width=32) (actual time=0.037..0.037 rows=1 loops=1) Filter: ((getcontexts)::text <> '00000001'::text)
27.	0.437	3.207	↑ 1.3	799	1	→ Subquery Scan "SELECT 2" (cost=0.00..270.00 rows=1000 width=32) (actual time=2.561..3.207 rows=799 loops=1)
28.	2.770	2.770	↑ 1.3	799	1	→ Function Scan on gethighercontexts (cost=0.00..260.00 rows=1000 width=32) (actual time=2.560..2.770 rows=799 loops=1)
29.	0.033	0.066	↑ 1.0	1	33	→ Subquery Scan "SELECT 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=33)
30.	0.033	0.033	↑ 1.0	1	33	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=33)
31.						SubPlan (forSubquery Scan base_plus_between_contexts)
32.	0.132	124.806	↑ 1.0	1	33	→ Aggregate (cost=550.00..550.01 rows=1 width=32) (actual time=3.782..3.782 rows=1 loops=33)
33.	0.099	124.674	↑ 200.0	1	33	→ Subquery Scan between_contexts (cost=0.00..549.50 rows=200 width=32) (actual time=3.776..3.778 rows=1 loops=33)
34.	8.283	124.575	↑ 200.0	1	33	→ HashSetOp Intersect (cost=0.00..547.50 rows=200 width=32) (actual time=3.775..3.775 rows=1 loops=33)
35.	12.078	116.292	↑ 2.5	801	33	→ Append (cost=0.00..542.50 rows=2000 width=32) (actual time=0.003..3.524 rows=801 loops=33)
36.	0.066	0.132	↑ 500.0	2	33	→ Subquery Scan "SELECT 1" (cost=0.00..270.00 rows=1000 width=32) (actual time=0.002..0.004 rows=2 loops=33)
37.	0.066	0.066	↑ 500.0	2	33	→ Function Scan on getcontexts (cost=0.00..260.00 rows=1000 width=32) (actual time=0.001..0.002 rows=2 loops=33)
38.	104.082	104.082	↑ 1.3	799	33	→ Subquery Scan "SELECT 2" (cost=0.00..272.50 rows=1000 width=32) (actual time=2.504..3.154 rows=799 loops=33) -> Function Scan on gethighercontexts (cost=0.00..262.50 rows=1000 width=32) (actual time=2.493..2.707 rows=799 loops=3
39.	1.122	1.122	↑ 32.3	31	33	→ Function Scan on gethighercontexts (cost=0.00..260.00 rows=1000 width=32) (actual time=0.022..0.034 rows=31 loops=33)
40.	0.033	0.066	↑ 1.0	1	33	→ Subquery Scan "SELECT 3" (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=33)
41.	0.033	0.033	↑ 1.0	1	33	→ Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=33)

                       QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------
 Hash Join  (cost=111713.06..204500.51 rows=1145877 width=217) (actual time=188.185..2804.350 rows=880770 loops=1)
   Hash Cond: ((promotable_paths.owner)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
   ->  Seq Scan on asset_link_tree promotable_paths  (cost=0.00..62246.62 rows=763282 width=85) (actual time=0.102..1458.433 rows=880770 loops=1)
         Filter: (((path)::text ~~ '/1/2/18/%'::text) AND ((owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,01000301,00010201,01010101
,01020001,01010001,00000001,01020201,00020301,00000101,00030001,01020101,01030001,01000001,00000000,00030201,00000201,00010001,00020101,01000101,01030201,01030301,00010301,01010301
,01020301,00020201}'::text[])))
   ->  Hash  (cost=111712.99..111712.99 rows=6 width=164) (actual time=188.070..188.070 rows=33 loops=1)
         ->  Nested Loop  (cost=849.27..111712.99 rows=6 width=164) (actual time=61.831..188.021 rows=33 loops=1)
               Join Filter: ((sub_path_inactive_contributor_map.child_path_owner)::text = (base_plus_between_contexts.context)::text)
               ->  Hash Join  (cost=849.27..861.99 rows=1 width=100) (actual time=53.355..56.851 rows=33 loops=1)
                     Hash Cond: ((moo.related_context)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
                     ->  Append  (cost=574.54..586.50 rows=200 width=64) (actual time=46.776..49.971 rows=800 loops=1)
                           ->  GroupAggregate  (cost=574.54..584.49 rows=199 width=64) (actual time=46.776..49.601 rows=799 loops=1)
                                 ->  Sort  (cost=574.54..577.03 rows=995 width=64) (actual time=46.768..47.464 rows=2368 loops=1)
                                       Sort Key: moo.related_context
                                       Sort Method:  quicksort  Memory: 157kB
                                       ->  Subquery Scan moo  (cost=0.00..525.00 rows=995 width=64) (actual time=4.619..34.882 rows=2368 loops=1)
                                             Filter: ((moo.related_context)::text <> (moo.subcontext)::text)
                                             ->  Function Scan on getrelatedcontexts  (cost=0.00..512.50 rows=1000 width=32) (actual time=4.617..32.752 rows=3168 loops=1)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
                     ->  Hash  (cost=274.71..274.71 rows=1 width=36) (actual time=6.573..6.573 rows=33 loops=1)
                           ->  Subquery Scan sub_path_inactive_contributor_map  (cost=274.70..274.71 rows=1 width=36) (actual time=6.486..6.551 rows=33 loops=1)
                                 ->  Unique  (cost=274.70..274.70 rows=1 width=48) (actual time=6.484..6.523 rows=33 loops=1)
                                       ->  Sort  (cost=274.70..274.70 rows=1 width=48) (actual time=6.484..6.496 rows=33 loops=1)
                                             Sort Key: source_paths.child_path_owner, source_paths.source_path_owner
                                             Sort Method:  quicksort  Memory: 19kB
                                             ->  Subquery Scan source_paths  (cost=0.00..274.69 rows=1 width=48) (actual time=4.186..6.388 rows=33 loops=1)
                                                   Filter: ((source_paths.child_path_owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,0
1000301,00010201,01010101,01020001,01010001,00000001,01020201,00020301,00000101,00030001,01020101,01030001,01000001,00000000,00030201,00000201,00010001,00020101,01000101,01030201,0
1030301,00010301,01010301,01020301,00020201}'::text[]))
                                                   ->  Index Scan using asset_link_tree_pkey on asset_link_tree  (cost=0.00..274.63 rows=1 width=13) (actual time=4.168..4.560 rows=
800 loops=1)
                                                         Index Cond: ((master_assetid = 18) AND ((path)::text = '/1/2/'::text))
                                                         Filter: ((owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,01000301,00010201,0
1010101,01020001,01010001,00000001,01020201,00020301,00000101,00030001,01020101,01030001,01000001,00000000,00030201,00000201,00010001,00020101,01000101,01030201,01030301,00010301,0
1010301,01020301,00020201}'::text[]))
                                                         SubPlan 2
                                                           ->  Aggregate  (cost=265.00..265.01 rows=1 width=32) (actual time=4.140..4.140 rows=1 loops=1)
                                                                 ->  Function Scan on gethighercontexts  (cost=0.00..262.50 rows=1000 width=32) (actual time=3.452..3.745 rows=799 l
oops=1)
               ->  Append  (cost=0.00..110823.98 rows=1201 width=32) (actual time=3.906..3.961 rows=33 loops=33)
                     ->  Subquery Scan base_plus_between_contexts  (cost=0.00..110551.96 rows=200 width=32) (actual time=3.905..3.908 rows=1 loops=33)
                           ->  Append  (cost=0.00..547.46 rows=200 width=32) (actual time=0.120..0.122 rows=1 loops=33)
                                 ->  Result  (cost=0.00..547.44 rows=199 width=32) (actual time=0.118..0.118 rows=0 loops=33)
                                       ->  HashSetOp Intersect  (cost=0.00..547.44 rows=199 width=32) (actual time=0.118..0.118 rows=0 loops=33)
                                             ->  Append  (cost=0.00..542.45 rows=1995 width=32) (actual time=0.039..3.627 rows=800 loops=1)
                                                   ->  Subquery Scan "*SELECT* 1"  (cost=0.00..272.45 rows=995 width=32) (actual time=0.038..0.039 rows=1 loops=1)
                                                         ->  Function Scan on getcontexts  (cost=0.00..262.50 rows=995 width=32) (actual time=0.037..0.037 rows=1 loops=1)
                                                               Filter: ((getcontexts)::text <> '00000001'::text)
                                                   ->  Subquery Scan "*SELECT* 2"  (cost=0.00..270.00 rows=1000 width=32) (actual time=2.561..3.207 rows=799 loops=1)
                                                         ->  Function Scan on gethighercontexts  (cost=0.00..260.00 rows=1000 width=32) (actual time=2.560..2.770 rows=799 loops=1)
                                 ->  Subquery Scan "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=33)
                                       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=33)
                           SubPlan 1
                             ->  Aggregate  (cost=550.00..550.01 rows=1 width=32) (actual time=3.782..3.782 rows=1 loops=33)
                                   ->  Subquery Scan between_contexts  (cost=0.00..549.50 rows=200 width=32) (actual time=3.776..3.778 rows=1 loops=33)
                                         ->  HashSetOp Intersect  (cost=0.00..547.50 rows=200 width=32) (actual time=3.775..3.775 rows=1 loops=33)
                                               ->  Append  (cost=0.00..542.50 rows=2000 width=32) (actual time=0.003..3.524 rows=801 loops=33)
                                                     ->  Subquery Scan "*SELECT* 1"  (cost=0.00..270.00 rows=1000 width=32) (actual time=0.002..0.004 rows=2 loops=33)
                                                           ->  Function Scan on getcontexts  (cost=0.00..260.00 rows=1000 width=32) (actual time=0.001..0.002 rows=2 loops=33)
                                                     ->  Subquery Scan "*SELECT* 2"  (cost=0.00..272.50 rows=1000 width=32) (actual time=2.504..3.154 rows=799 loops=33)
                                                           ->  Function Scan on gethighercontexts  (cost=0.00..262.50 rows=1000 width=32) (actual time=2.493..2.707 rows=799 loops=3
3)
                     ->  Function Scan on gethighercontexts  (cost=0.00..260.00 rows=1000 width=32) (actual time=0.022..0.034 rows=31 loops=33)
                     ->  Subquery Scan "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=33)
                           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=33)

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	4.272 ms	0.2 %
Append	5	13.455 ms	0.5 %
Function Scan	5	36.747 ms	1.3 %
GroupAggregate	1	2.137 ms	0.1 %
Hash	2	0.071 ms	0.0 %
Hash Join	2	1158.154 ms	41.3 %
HashSetOp Intersect	2	8.550 ms	0.3 %
Nested Loop	1	0.457 ms	0.0 %
Result	4	0.067 ms	0.0 %
Seq Scan	1	1458.433 ms	52.0 %
Sort	2	12.690 ms	0.5 %
Subquery Scan "SELECT 1"	2	0.068 ms	0.0 %
Subquery Scan "SELECT 2"	2	104.519 ms	3.7 %
Subquery Scan "SELECT 3"	2	0.066 ms	0.0 %
Subquery Scan base_plus_between_contexts	1	0.132 ms	0.0 %
Subquery Scan between_contexts	1	0.099 ms	0.0 %
Subquery Scan moo	1	2.130 ms	0.1 %
Subquery Scan source_paths	1	2.248 ms	0.1 %
Subquery Scan sub_path_inactive_contributor_map	1	0.028 ms	0.0 %
Unique	1	0.027 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
asset_link_tree	1	1458.433 ms	52.0 %
Seq Scan	1	1458.433 ms	100.0 %

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: cw7

Colorize:

Columns visibility:

Per node type stats

Per table stats