explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gg7n

Settings
# exclusive inclusive rows x rows loops node
1. 124.105 116,036.526 ↓ 49.8 10,299 1

Nested Loop Left Join (cost=165,479.66..256,813.83 rows=207 width=299) (actual time=9,171.968..116,036.526 rows=10,299 loops=1)

2.          

CTE ancestorfiltercte

3. 156.692 2,939.666 ↓ 330.2 89,483 1

Recursive Union (cost=0.00..2,834.78 rows=271 width=16) (actual time=0.019..2,939.666 rows=89,483 loops=1)

4. 0.078 0.078 ↑ 1.0 1 1

Seq Scan on article_tree_node_attribute definedatt1_ (cost=0.00..7.56 rows=1 width=16) (actual time=0.016..0.078 rows=1 loops=1)

  • Filter: (attribute = 'a229bde9-9c5b-0000-0000-000000000000'::uuid)
  • Rows Removed by Filter: 364
5. 381.802 2,782.896 ↓ 414.3 11,185 8

Nested Loop (cost=4.47..282.18 rows=27 width=16) (actual time=4.925..347.862 rows=11,185 loops=8)

6. 74.536 74.536 ↓ 1,118.5 11,185 8

WorkTable Scan on ancestorfiltercte rec (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..9.317 rows=11,185 loops=8)

7. 1,521.211 2,326.558 ↑ 3.0 1 89,483

Bitmap Heap Scan on article_tree_node (cost=4.47..28.17 rows=3 width=32) (actual time=0.019..0.026 rows=1 loops=89,483)

  • Recheck Cond: (parent_id = rec.article_tree_node_id)
  • Filter: (type <> 3)
  • Rows Removed by Filter: 2
  • Heap Blocks: exact=219992
8. 805.347 805.347 ↑ 2.0 3 89,483

Bitmap Index Scan on ix_article_tree_node_parent_id (cost=0.00..4.47 rows=6 width=0) (actual time=0.009..0.009 rows=3 loops=89,483)

  • Index Cond: (parent_id = rec.article_tree_node_id)
9.          

CTE articletreestartnodecte

10. 6.288 4,661.592 ↓ 43.5 5,000 1

Limit (cost=2,215.98..2,216.27 rows=115 width=16) (actual time=4,651.316..4,661.592 rows=5,000 loops=1)

11. 141.092 4,655.304 ↓ 43.5 5,000 1

Sort (cost=2,215.98..2,216.27 rows=115 width=16) (actual time=4,651.313..4,655.304 rows=5,000 loops=1)

  • Sort Key: ancestorfi0_.article_tree_node_id
  • Sort Method: top-N heapsort Memory: 619kB
12. 389.793 4,514.212 ↓ 746.5 85,849 1

Nested Loop (cost=0.42..2,212.05 rows=115 width=16) (actual time=18.657..4,514.212 rows=85,849 loops=1)

13. 3,140.106 3,140.106 ↓ 330.2 89,483 1

CTE Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..5.42 rows=271 width=16) (actual time=0.022..3,140.106 rows=89,483 loops=1)

14. 984.313 984.313 ↑ 1.0 1 89,483

Index Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..8.13 rows=1 width=16) (actual time=0.010..0.011 rows=1 loops=89,483)

  • Index Cond: (id = ancestorfi0_.article_tree_node_id)
  • Filter: (type = 2)
  • Rows Removed by Filter: 0
15.          

CTE articletreenodecte

16. 226.468 939.701 ↓ 2.4 27,935 1

Recursive Union (cost=0.42..85,256.85 rows=11,615 width=113) (actual time=0.039..939.701 rows=27,935 loops=1)

17. 14.975 74.345 ↓ 43.5 5,000 1

Nested Loop (cost=0.42..962.05 rows=115 width=113) (actual time=0.031..74.345 rows=5,000 loops=1)

18. 4.370 4.370 ↓ 43.5 5,000 1

CTE Scan on articletreestartnodecte articletre0_ (cost=0.00..2.30 rows=115 width=16) (actual time=0.001..4.370 rows=5,000 loops=1)

19. 55.000 55.000 ↑ 1.0 1 5,000

Index Scan using pk_article_tree_node on article_tree_node articletre1__1 (cost=0.42..8.34 rows=1 width=93) (actual time=0.010..0.011 rows=1 loops=5,000)

  • Index Cond: (id = articletre0_.article_tree_node_id)
20. 274.571 638.888 ↓ 2.5 2,867 8

Nested Loop (cost=0.42..8,406.25 rows=1,150 width=84) (actual time=0.085..79.861 rows=2,867 loops=8)

21. 57.032 57.032 ↓ 3.0 3,492 8

WorkTable Scan on articletreenodecte articletre0__1 (cost=0.00..23.00 rows=1,150 width=68) (actual time=0.002..7.129 rows=3,492 loops=8)

22. 307.285 307.285 ↑ 1.0 1 27,935

Index Scan using pk_article_tree_node on article_tree_node articletre1__2 (cost=0.42..7.27 rows=1 width=93) (actual time=0.009..0.011 rows=1 loops=27,935)

  • Index Cond: (id = articletre0__1.parent_id)
23. 51.704 11,532.056 ↓ 49.8 10,299 1

Nested Loop Left Join (cost=75,171.34..109,509.16 rows=207 width=204) (actual time=9,155.397..11,532.056 rows=10,299 loops=1)

24. 55.456 11,344.650 ↓ 46.8 9,693 1

Nested Loop Left Join (cost=75,170.92..109,409.50 rows=207 width=183) (actual time=9,155.367..11,344.650 rows=9,693 loops=1)

25. 37.982 10,979.018 ↓ 46.8 9,693 1

Hash Left Join (cost=75,170.50..109,300.16 rows=207 width=183) (actual time=9,154.226..10,979.018 rows=9,693 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre4_.origin_article_tree_node_id)
26. 296.839 9,674.780 ↓ 46.8 9,693 1

Hash Semi Join (cost=74,240.09..108,118.65 rows=207 width=151) (actual time=7,887.944..9,674.780 rows=9,693 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre9_.article_tree_node_id)
27. 1,005.969 4,705.946 ↑ 1.2 278,990 1

Hash Right Join (cost=74,236.35..107,210.47 rows=343,675 width=183) (actual time=3,215.587..4,705.946 rows=278,990 loops=1)

  • Hash Cond: (children1_.parent_id = mainarticl0_.id)
28. 484.900 484.900 ↑ 1.0 448,681 1

Seq Scan on article_tree_node children1_ (cost=0.00..17,250.81 rows=448,681 width=32) (actual time=0.107..484.900 rows=448,681 loops=1)

29. 254.012 3,215.077 ↑ 1.0 190,631 1

Hash (cost=67,385.46..67,385.46 rows=190,631 width=167) (actual time=3,215.076..3,215.077 rows=190,631 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2433kB
30. 910.776 2,961.065 ↑ 1.0 190,631 1

Hash Join (cost=27,527.79..67,385.46 rows=190,631 width=167) (actual time=1,594.846..2,961.065 rows=190,631 loops=1)

  • Hash Cond: (mainarticl0_2_.id = mainarticl0_.id)
31. 455.689 455.689 ↑ 1.0 448,681 1

Seq Scan on article_tree_node mainarticl0_2_ (cost=0.00..17,250.81 rows=448,681 width=132) (actual time=0.042..455.689 rows=448,681 loops=1)

32. 192.108 1,594.600 ↑ 1.0 190,631 1

Hash (cost=23,654.90..23,654.90 rows=190,631 width=35) (actual time=1,594.599..1,594.600 rows=190,631 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3630kB
33. 703.585 1,402.492 ↑ 1.0 190,631 1

Hash Join (cost=6,647.20..23,654.90 rows=190,631 width=35) (actual time=322.611..1,402.492 rows=190,631 loops=1)

  • Hash Cond: (mainarticl0_1_.id = mainarticl0_.id)
34. 376.465 376.465 ↑ 1.0 445,047 1

Seq Scan on selectable_article mainarticl0_1_ (cost=0.00..7,285.47 rows=445,047 width=19) (actual time=0.012..376.465 rows=445,047 loops=1)

35. 171.598 322.442 ↑ 1.0 190,631 1

Hash (cost=3,333.31..3,333.31 rows=190,631 width=16) (actual time=322.442..322.442 rows=190,631 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 3264kB
36. 150.844 150.844 ↑ 1.0 190,631 1

Seq Scan on main_article mainarticl0_ (cost=0.00..3,333.31 rows=190,631 width=16) (actual time=0.010..150.844 rows=190,631 loops=1)

37. 3.730 4,671.995 ↓ 43.5 5,000 1

Hash (cost=2.30..2.30 rows=115 width=16) (actual time=4,671.994..4,671.995 rows=5,000 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 299kB
38. 4,668.265 4,668.265 ↓ 43.5 5,000 1

CTE Scan on articletreestartnodecte articletre9_ (cost=0.00..2.30 rows=115 width=16) (actual time=4,651.320..4,668.265 rows=5,000 loops=1)

39. 9.012 1,266.256 ↑ 1.9 5,000 1

Hash (cost=809.42..809.42 rows=9,679 width=48) (actual time=1,266.256..1,266.256 rows=5,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1774kB
40. 82.740 1,257.244 ↑ 1.9 5,000 1

Hash Anti Join (cost=377.49..809.42 rows=9,679 width=48) (actual time=1,167.884..1,257.244 rows=5,000 loops=1)

  • Hash Cond: (articletre4_.origin_article_tree_node_id = articletre5_.origin_article_tree_node_id)
  • Join Filter: (articletre5_.level > articletre4_.level)
  • Rows Removed by Join Filter: 98285
41. 36.442 36.442 ↓ 2.4 27,935 1

CTE Scan on articletreenodecte articletre4_ (cost=0.00..232.30 rows=11,615 width=52) (actual time=0.043..36.442 rows=27,935 loops=1)

42. 45.600 1,138.062 ↓ 2.4 27,935 1

Hash (cost=232.30..232.30 rows=11,615 width=20) (actual time=1,138.062..1,138.062 rows=27,935 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 1675kB
43. 1,092.462 1,092.462 ↓ 2.4 27,935 1

CTE Scan on articletreenodecte articletre5_ (cost=0.00..232.30 rows=11,615 width=20) (actual time=0.001..1,092.462 rows=27,935 loops=1)

44. 310.176 310.176 ↑ 1.0 1 9,693

Index Scan using pk_vendor_article on vendor_article children1_5_ (cost=0.42..0.52 rows=1 width=32) (actual time=0.030..0.032 rows=1 loops=9,693)

  • Index Cond: (children1_.id = id)
45. 135.702 135.702 ↑ 1.0 1 9,693

Index Scan using main_article_normalized_price_pkey on main_article_normalized_price normalized2_ (cost=0.42..0.47 rows=1 width=37) (actual time=0.012..0.014 rows=1 loops=9,693)

  • Index Cond: (mainarticl0_.id = main_article_id)
46. 154.485 154.485 ↑ 1.0 1 10,299

Index Scan using pk_article_tree_node on article_tree_node articletre3_ (cost=0.42..0.57 rows=1 width=75) (actual time=0.011..0.015 rows=1 loops=10,299)

  • Index Cond: (mainarticl0_2_.parent_id = id)
47.          

SubPlan (forNested Loop Left Join)

48. 267.774 104,225.880 ↑ 1.0 1 10,299

Aggregate (cost=274.75..274.76 rows=1 width=32) (actual time=10.119..10.120 rows=1 loops=10,299)

49. 308.279 103,958.106 ↑ 8.6 11 10,299

Hash Join (cost=11.21..274.51 rows=95 width=16) (actual time=4.951..10.094 rows=11 loops=10,299)

  • Hash Cond: (articletre6_.article_tree_node_id = definedatt7_.node_id)
50. 103,649.136 103,649.136 ↑ 9.7 6 10,299

CTE Scan on articletreenodecte articletre6_ (cost=0.00..261.34 rows=58 width=16) (actual time=0.869..10.064 rows=6 loops=10,299)

  • Filter: (origin_article_tree_node_id = mainarticl0_.id)
  • Rows Removed by Filter: 27929
51. 0.339 0.691 ↑ 1.0 365 1

Hash (cost=6.65..6.65 rows=365 width=32) (actual time=0.690..0.691 rows=365 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
52. 0.352 0.352 ↑ 1.0 365 1

Seq Scan on article_tree_node_attribute definedatt7_ (cost=0.00..6.65 rows=365 width=32) (actual time=0.013..0.352 rows=365 loops=1)

Planning time : 8.155 ms
Execution time : 116,056.262 ms