explain.depesz.com

A tool for finding a real cause for slow queries.

Result: cw7

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 1,157.847 2,804.350 ↑ 1.3 880,770 1

Hash Join (cost=111,713.06..204,500.51 rows=1,145,877 width=217) (actual time=188.185..2,804.350 rows=880,770 loops=1)

  • Hash Cond: ((promotable_paths.owner)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
2. 1,458.433 1,458.433 ↓ 1.2 880,770 1

Seq Scan on asset_link_tree promotable_paths (cost=0.00..62,246.62 rows=763,282 width=85) (actual time=0.102..1,458.433 rows=880,770 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=111,712.99..111,712.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..111,712.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 2,368 1

Sort (cost=574.54..577.03 rows=995 width=64) (actual time=46.768..47.464 rows=2,368 loops=1)

  • Sort Key: moo.related_context
  • Sort Method: quicksort Memory: 157kB
9. 2.130 34.882 ↓ 2.4 2,368 1

Subquery Scan moo (cost=0.00..525.00 rows=995 width=64) (actual time=4.619..34.882 rows=2,368 loops=1)

  • Filter: ((moo.related_context)::text <> (moo.subcontext)::text)
10. 32.752 32.752 ↓ 3.2 3,168 1

Function Scan on getrelatedcontexts (cost=0.00..512.50 rows=1,000 width=32) (actual time=4.617..32.752 rows=3,168 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..110,823.98 rows=1,201 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..110,551.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=1,995 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=1,000 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=1,000 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=2,000 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=1,000 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=1,000 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=1,000 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=1,000 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)