explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RzDV

Settings
# exclusive inclusive rows x rows loops node
1. 451.777 26,621.463 ↓ 765.0 158,358 1

Nested Loop Left Join (cost=165,475.44..199,933.25 rows=207 width=267) (actual time=17,731.624..26,621.463 rows=158,358 loops=1)

2.          

CTE ancestorfiltercte

3. 157.055 2,738.308 ↓ 330.2 89,483 1

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

4. 0.093 0.093 ↑ 1.0 1 1

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

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

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

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

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

7. 1,431.728 2,147.592 ↑ 3.0 1 89,483

Bitmap Heap Scan on article_tree_node (cost=4.47..28.17 rows=3 width=32) (actual time=0.018..0.024 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. 715.864 715.864 ↑ 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.008..0.008 rows=3 loops=89,483)

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

CTE articletreestartnodecte

10. 236.400 4,049.017 ↓ 746.5 85,849 1

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

11. 2,917.787 2,917.787 ↓ 330.2 89,483 1

CTE Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..5.42 rows=271 width=16) (actual time=0.026..2,917.787 rows=89,483 loops=1)

12. 894.830 894.830 ↑ 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.009..0.010 rows=1 loops=89,483)

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

CTE articletreenodecte

14. 2,059.081 6,869.354 ↓ 42.4 492,956 1

Recursive Union (cost=0.42..85,256.85 rows=11,615 width=113) (actual time=0.061..6,869.354 rows=492,956 loops=1)

15. 195.399 1,021.049 ↓ 746.5 85,849 1

Nested Loop (cost=0.42..962.05 rows=115 width=113) (actual time=0.053..1,021.049 rows=85,849 loops=1)

16. 53.009 53.009 ↓ 746.5 85,849 1

CTE Scan on articletreestartnodecte articletre0_ (cost=0.00..2.30 rows=115 width=16) (actual time=0.019..53.009 rows=85,849 loops=1)

17. 772.641 772.641 ↑ 1.0 1 85,849

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.008..0.009 rows=1 loops=85,849)

  • Index Cond: (id = articletre0_.article_tree_node_id)
18. 1,865.108 3,789.224 ↓ 44.3 50,888 8

Nested Loop (cost=0.42..8,406.25 rows=1,150 width=84) (actual time=23.729..473.653 rows=50,888 loops=8)

19. 445.248 445.248 ↓ 53.6 61,620 8

WorkTable Scan on articletreenodecte articletre0__1 (cost=0.00..23.00 rows=1,150 width=68) (actual time=0.033..55.656 rows=61,620 loops=8)

20. 1,478.868 1,478.868 ↑ 1.0 1 492,956

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.003..0.003 rows=1 loops=492,956)

  • Index Cond: (id = articletre0__1.parent_id)
21. 356.307 24,902.822 ↓ 765.0 158,358 1

Nested Loop Left Join (cost=75,171.34..109,509.16 rows=207 width=204) (actual time=17,731.607..24,902.822 rows=158,358 loops=1)

22. 375.861 22,897.681 ↓ 724.1 149,894 1

Nested Loop Left Join (cost=75,170.92..109,409.50 rows=207 width=183) (actual time=17,731.001..22,897.681 rows=149,894 loops=1)

23. 502.825 19,673.834 ↓ 724.1 149,894 1

Hash Left Join (cost=75,170.50..109,300.16 rows=207 width=183) (actual time=17,728.600..19,673.834 rows=149,894 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre4_.origin_article_tree_node_id)
24. 456.974 9,318.272 ↓ 724.1 149,894 1

Hash Semi Join (cost=74,240.09..108,118.65 rows=207 width=151) (actual time=7,874.740..9,318.272 rows=149,894 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre9_.article_tree_node_id)
25. 669.421 4,533.993 ↑ 1.2 278,990 1

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

  • Hash Cond: (children1_.parent_id = mainarticl0_.id)
26. 317.366 317.366 ↑ 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.041..317.366 rows=448,681 loops=1)

27. 295.816 3,547.206 ↑ 1.0 190,631 1

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

  • Buckets: 32768 Batches: 16 Memory Usage: 2433kB
28. 944.633 3,251.390 ↑ 1.0 190,631 1

Hash Join (cost=27,527.79..67,385.46 rows=190,631 width=167) (actual time=1,921.255..3,251.390 rows=190,631 loops=1)

  • Hash Cond: (mainarticl0_2_.id = mainarticl0_.id)
29. 385.745 385.745 ↑ 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.012..385.745 rows=448,681 loops=1)

30. 208.727 1,921.012 ↑ 1.0 190,631 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3630kB
31. 770.661 1,712.285 ↑ 1.0 190,631 1

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

  • Hash Cond: (mainarticl0_1_.id = mainarticl0_.id)
32. 467.519 467.519 ↑ 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.045..467.519 rows=445,047 loops=1)

33. 251.095 474.105 ↑ 1.0 190,631 1

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

  • Buckets: 131072 Batches: 4 Memory Usage: 3264kB
34. 223.010 223.010 ↑ 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.014..223.010 rows=190,631 loops=1)

35. 96.926 4,327.305 ↓ 746.5 85,849 1

Hash (cost=2.30..2.30 rows=115 width=16) (actual time=4,327.305..4,327.305 rows=85,849 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3073kB
36. 4,230.379 4,230.379 ↓ 746.5 85,849 1

CTE Scan on articletreestartnodecte articletre9_ (cost=0.00..2.30 rows=115 width=16) (actual time=5.988..4,230.379 rows=85,849 loops=1)

37. 83.505 9,852.737 ↓ 8.9 85,849 1

Hash (cost=809.42..809.42 rows=9,679 width=48) (actual time=9,852.737..9,852.737 rows=85,849 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 8 (originally 1) Memory Usage: 3969kB
38. 1,032.439 9,769.232 ↓ 8.9 85,849 1

Hash Anti Join (cost=377.49..809.42 rows=9,679 width=48) (actual time=8,633.323..9,769.232 rows=85,849 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: 786869
39. 363.562 363.562 ↓ 42.4 492,956 1

CTE Scan on articletreenodecte articletre4_ (cost=0.00..232.30 rows=11,615 width=52) (actual time=0.065..363.562 rows=492,956 loops=1)

40. 418.410 8,373.231 ↓ 42.4 492,956 1

Hash (cost=232.30..232.30 rows=11,615 width=20) (actual time=8,373.231..8,373.231 rows=492,956 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 16 (originally 1) Memory Usage: 3329kB
41. 7,954.821 7,954.821 ↓ 42.4 492,956 1

CTE Scan on articletreenodecte articletre5_ (cost=0.00..232.30 rows=11,615 width=20) (actual time=0.001..7,954.821 rows=492,956 loops=1)

42. 2,847.986 2,847.986 ↑ 1.0 1 149,894

Index Scan using pk_vendor_article on vendor_article children1_5_ (cost=0.42..0.52 rows=1 width=32) (actual time=0.018..0.019 rows=1 loops=149,894)

  • Index Cond: (children1_.id = id)
43. 1,648.834 1,648.834 ↑ 1.0 1 149,894

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.010..0.011 rows=1 loops=149,894)

  • Index Cond: (mainarticl0_.id = main_article_id)
44. 1,266.864 1,266.864 ↑ 1.0 1 158,358

Index Scan using pk_article_tree_node on article_tree_node articletre3_ (cost=0.42..0.57 rows=1 width=75) (actual time=0.007..0.008 rows=1 loops=158,358)

  • Index Cond: (mainarticl0_2_.parent_id = id)
Planning time : 3.332 ms
Execution time : 26,807.904 ms