explain.depesz.com

A tool for finding a real cause for slow queries.

Result: bqb

options
# exclusive inclusive rows x rows loops node
1. 2526.479 16059.287 ↓ 9.4 880770 1
→

WindowAgg (cost=106341.39..108224.67 rows=94164 width=317) (actual time=12985.298..16059.287 rows=880770 loops=1)

2.          

CTE lowest_promoted_paths_cleaned
3. 921.857 2545.634 ↓ 9.4 880770 1
→

Hash Join (cost=274.73..64873.65 rows=94164 width=87) (actual time=7.035..2545.634 rows=880770 loops=1)

  • Hash Cond: ((promotable_paths.owner)::text = (sub_path_inactive_contributor_map.child_path_owner)::text)
4. 1616.893 1616.893 ↓ 2.3 880770 1
→

Seq Scan on asset_link_tree promotable_paths (cost=0.00..62009.42 rows=376654 width=83) (actual time=0.141..1616.893 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,0
5. 0.020 6.884 ↓ 33.0 33 1
→

Hash (cost=274.71..274.71 rows=1 width=36) (actual time=6.884..6.884 rows=33 loops=1)

6. 0.027 6.864 ↓ 33.0 33 1
→

Subquery Scan sub_path_inactive_contributor_map (cost=274.70..274.71 rows=1 width=36) (actual time=6.800..6.864 rows=33 loops=1)

7. 0.028 6.837 ↓ 33.0 33 1
→

Unique (cost=274.70..274.70 rows=1 width=48) (actual time=6.799..6.837 rows=33 loops=1)

8. 0.112 6.809 ↓ 33.0 33 1
→

Sort (cost=274.70..274.70 rows=1 width=48) (actual time=6.798..6.809 rows=33 loops=1)

  • Sort Key: source_paths.child_path_owner, source_paths.source_path_owner
  • Sort Method: quicksort Memory: 19kB
9. 1.977 6.697 ↓ 33.0 33 1
→

Subquery Scan source_paths (cost=0.00..274.69 rows=1 width=48) (actual time=4.383..6.697 rows=33 loops=1)

  • Filter: ((source_paths.child_path_owner)::text = ANY ('{00020001,01010201,00000301,01030101,00030301,01000201,00030101,00010101,01000301,00010201,01010101,010
10. 0.397 4.720 ↓ 800.0 800 1
→

Index Scan using asset_link_tree_pkey on asset_link_tree (cost=0.00..274.63 rows=1 width=13) (actual time=4.353..4.720 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,01010101,01020001,01010001,000
11.          

SubPlan (forIndex Scan)
12. 0.435 4.323 ↑ 1.0 1 1
→

Aggregate (cost=265.00..265.01 rows=1 width=32) (actual time=4.323..4.323 rows=1 loops=1)

13. 3.888 3.888 ↑ 1.3 799 1
→

Function Scan on gethighercontexts (cost=0.00..262.50 rows=1000 width=32) (actual time=3.588..3.888 rows=799 loops=1)

14. 7335.505 13532.808 ↓ 9.4 880770 1
→

Sort (cost=41467.74..41703.15 rows=94164 width=317) (actual time=12985.263..13532.808 rows=880770 loops=1)

  • Sort Key: lowest_promoted_paths_cleaned.master_assetid, lowest_promoted_paths_cleaned.path
  • Sort Method: external sort Disk: 94512kB
15. 896.418 6197.303 ↓ 9.4 880770 1
→

Hash Join (cost=589.00..6003.43 rows=94164 width=317) (actual time=60.073..6197.303 rows=880770 loops=1)

  • Hash Cond: ((lowest_promoted_paths_cleaned.promotable_owner)::text = ("*SELECT* 1".context)::text)
16. 5247.859 5247.859 ↓ 9.4 880770 1
→

CTE Scan on lowest_promoted_paths_cleaned (cost=0.00..1883.28 rows=94164 width=285) (actual time=7.039..5247.859 rows=880770 loops=1)

17. 0.575 53.026 ↓ 4.0 800 1
→

Hash (cost=586.50..586.50 rows=200 width=64) (actual time=53.026..53.026 rows=800 loops=1)

18. 0.421 52.451 ↓ 4.0 800 1
→

Append (cost=574.54..586.50 rows=200 width=64) (actual time=48.590..52.451 rows=800 loops=1)

19. 0.456 52.028 ↓ 4.0 799 1
→

Subquery Scan "*SELECT* 1" (cost=574.54..586.48 rows=199 width=64) (actual time=48.589..52.028 rows=799 loops=1)

20. 2.323 51.572 ↓ 4.0 799 1
→

GroupAggregate (cost=574.54..584.49 rows=199 width=64) (actual time=48.588..51.572 rows=799 loops=1)

21. 12.707 49.249 ↓ 2.4 2368 1
→

Sort (cost=574.54..577.03 rows=995 width=64) (actual time=48.581..49.249 rows=2368 loops=1)

  • Sort Key: moo.related_context
  • Sort Method: quicksort Memory: 157kB
22. 2.148 36.542 ↓ 2.4 2368 1
→

Subquery Scan moo (cost=0.00..525.00 rows=995 width=64) (actual time=5.049..36.542 rows=2368 loops=1)

  • Filter: ((moo.related_context)::text <> (moo.subcontext)::text)
23. 34.394 34.394 ↓ 3.2 3168 1
→

Function Scan on getrelatedcontexts (cost=0.00..512.50 rows=1000 width=32) (actual time=5.045..34.394 rows=3168 loops=1)

24. 0.001 0.002 ↑ 1.0 1 1
→

Subquery Scan "*SELECT* 2" (cost=0.00..0.02 rows=1 width=64) (actual time=0.001..0.002 rows=1 loops=1)

25. 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)