explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Gr9o

Settings
# exclusive inclusive rows x rows loops node
1. 0.583 22,261.900 ↓ 1.0 183 1

Nested Loop Left Join (cost=634,706.24..1,783,481.42 rows=180 width=267) (actual time=22,240.949..22,261.900 rows=183 loops=1)

2.          

CTE ancestorfiltercte

3. 2.556 12,785.311 ↑ 2,514.9 105 1

Recursive Union (cost=11.21..451,070.86 rows=264,063 width=16) (actual time=7,976.711..12,785.311 rows=105 loops=1)

4. 738.662 8,922.081 ↑ 973.0 1 1

Hash Left Join (cost=11.21..22,556.33 rows=973 width=16) (actual time=7,976.709..8,922.081 rows=1 loops=1)

  • Hash Cond: (articletre0_.id = definedatt1_.node_id)
  • Filter: ((SubPlan 1) = 'a229bde9-9c5b-0000-0000-000000000000'::uuid)
  • Rows Removed by Filter: 194407
5. 600.990 600.990 ↑ 1.0 194,265 1

Seq Scan on article_tree_node articletre0_ (cost=0.00..18,372.51 rows=194,533 width=16) (actual time=0.016..600.990 rows=194,265 loops=1)

  • Filter: (type <> 3)
  • Rows Removed by Filter: 254416
6. 0.264 0.517 ↑ 1.0 365 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
7. 0.253 0.253 ↑ 1.0 365 1

Seq Scan on article_tree_node_attribute definedatt1_ (cost=0.00..6.65 rows=365 width=32) (actual time=0.011..0.253 rows=365 loops=1)

8.          

SubPlan (forHash Left Join)

9. 7,581.912 7,581.912 ↓ 0.0 0 194,408

Seq Scan on article_attribute a11 (cost=0.00..4.85 rows=1 width=16) (actual time=0.039..0.039 rows=0 loops=194,408)

  • Filter: (id = definedatt1_.attribute)
  • Rows Removed by Filter: 148
10. 80.564 3,860.674 ↑ 505.9 52 2

Merge Join (cost=40,956.03..42,323.33 rows=26,309 width=16) (actual time=1,930.194..1,930.337 rows=52 loops=2)

  • Merge Cond: (article_tree_node.parent_id = rec.article_tree_node_id)
11. 1,968.254 3,780.024 ↑ 5.8 33,659 2

Sort (cost=40,116.90..40,603.23 rows=194,533 width=32) (actual time=1,841.731..1,890.012 rows=33,659 loops=2)

  • Sort Key: article_tree_node.parent_id
  • Sort Method: external merge Disk: 6328kB
12. 1,811.770 1,811.770 ↑ 1.0 194,265 2

Seq Scan on article_tree_node (cost=0.00..18,372.51 rows=194,533 width=32) (actual time=0.440..905.885 rows=194,265 loops=2)

  • Filter: (type <> 3)
  • Rows Removed by Filter: 254416
13. 0.078 0.086 ↑ 194.6 50 2

Sort (cost=839.13..863.45 rows=9,730 width=16) (actual time=0.019..0.043 rows=50 loops=2)

  • Sort Key: rec.article_tree_node_id
  • Sort Method: quicksort Memory: 25kB
14. 0.008 0.008 ↑ 4,865.0 2 2

WorkTable Scan on ancestorfiltercte rec (cost=0.00..194.60 rows=9,730 width=16) (actual time=0.002..0.004 rows=2 loops=2)

15.          

CTE articletreestartnodecte

16. 0.109 12,786.628 ↑ 1.0 100 1

Limit (cost=0.42..163.01 rows=100 width=16) (actual time=12,785.151..12,786.628 rows=100 loops=1)

17. 0.237 12,786.519 ↑ 1,124.0 100 1

Nested Loop (cost=0.42..182,749.77 rows=112,403 width=16) (actual time=12,785.149..12,786.519 rows=100 loops=1)

18. 12,785.442 12,785.442 ↑ 2,514.9 105 1

CTE Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..5,281.26 rows=264,063 width=16) (actual time=7,976.713..12,785.442 rows=105 loops=1)

19. 0.840 0.840 ↑ 1.0 1 105

Index Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..0.66 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=105)

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

CTE articletreenodecte

21. 0.681 3.203 ↑ 33.7 300 1

Recursive Union (cost=0.42..75,351.00 rows=10,100 width=113) (actual time=0.042..3.203 rows=300 loops=1)

22. 0.224 1.100 ↑ 1.0 100 1

Nested Loop (cost=0.42..839.00 rows=100 width=113) (actual time=0.030..1.100 rows=100 loops=1)

23. 0.076 0.076 ↑ 1.0 100 1

CTE Scan on articletreestartnodecte articletre0__1 (cost=0.00..2.00 rows=100 width=16) (actual time=0.002..0.076 rows=100 loops=1)

24. 0.800 0.800 ↑ 1.0 1 100

Index Scan using pk_article_tree_node on article_tree_node articletre1__1 (cost=0.42..8.36 rows=1 width=93) (actual time=0.007..0.008 rows=1 loops=100)

  • Index Cond: (id = articletre0__1.article_tree_node_id)
25. 0.672 1.422 ↑ 14.9 67 3

Nested Loop (cost=0.42..7,431.00 rows=1,000 width=84) (actual time=0.076..0.474 rows=67 loops=3)

26. 0.150 0.150 ↑ 10.0 100 3

WorkTable Scan on articletreenodecte articletre0__2 (cost=0.00..20.00 rows=1,000 width=68) (actual time=0.002..0.050 rows=100 loops=3)

27. 0.600 0.600 ↑ 1.0 1 300

Index Scan using pk_article_tree_node on article_tree_node articletre1__2 (cost=0.42..7.40 rows=1 width=93) (actual time=0.002..0.002 rows=1 loops=300)

  • Index Cond: (id = articletre0__2.parent_id)
28. 0.420 22,260.585 ↓ 1.0 183 1

Nested Loop Left Join (cost=108,120.95..1,256,791.84 rows=180 width=204) (actual time=22,240.936..22,260.585 rows=183 loops=1)

29. 0.514 22,258.905 ↑ 1.0 180 1

Nested Loop Left Join (cost=108,120.54..1,256,705.18 rows=180 width=183) (actual time=22,240.917..22,258.905 rows=180 loops=1)

30. 0.312 22,243.631 ↑ 1.0 180 1

Hash Right Join (cost=108,120.12..1,256,610.10 rows=180 width=183) (actual time=22,228.822..22,243.631 rows=180 loops=1)

  • Hash Cond: (articletre4_.origin_article_tree_node_id = mainarticl0_.id)
31. 10.750 22.450 ↑ 50.5 100 1

CTE Scan on articletreenodecte articletre4_ (cost=0.00..1,148,471.00 rows=5,050 width=48) (actual time=7.944..22.450 rows=100 loops=1)

  • Filter: (SubPlan 5)
  • Rows Removed by Filter: 200
32.          

SubPlan (forCTE Scan)

33. 11.700 11.700 ↑ 16.7 3 300

CTE Scan on articletreenodecte articletre5_ (cost=0.00..227.25 rows=50 width=4) (actual time=0.006..0.039 rows=3 loops=300)

  • Filter: (origin_article_tree_node_id = articletre4_.origin_article_tree_node_id)
  • Rows Removed by Filter: 231
34. 0.455 22,220.869 ↑ 1.0 180 1

Hash (cost=108,117.87..108,117.87 rows=180 width=151) (actual time=22,220.869..22,220.869 rows=180 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
35. 529.741 22,220.414 ↑ 1.0 180 1

Hash Semi Join (cost=74,239.60..108,117.87 rows=180 width=151) (actual time=19,126.606..22,220.414 rows=180 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre9_.article_tree_node_id)
36. 1,847.476 8,903.848 ↑ 1.2 278,990 1

Hash Right Join (cost=74,236.35..107,210.47 rows=343,675 width=183) (actual time=6,194.390..8,903.848 rows=278,990 loops=1)

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

38. 433.889 6,194.067 ↑ 1.0 190,631 1

Hash (cost=67,385.46..67,385.46 rows=190,631 width=167) (actual time=6,194.066..6,194.067 rows=190,631 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2433kB
39. 1,445.998 5,760.178 ↑ 1.0 190,631 1

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

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

41. 375.469 3,628.533 ↑ 1.0 190,631 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3630kB
42. 1,699.284 3,253.064 ↑ 1.0 190,631 1

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

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

44. 481.688 884.359 ↑ 1.0 190,631 1

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

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

46. 0.098 12,786.825 ↑ 1.0 100 1

Hash (cost=2.00..2.00 rows=100 width=16) (actual time=12,786.825..12,786.825 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
47. 12,786.727 12,786.727 ↑ 1.0 100 1

CTE Scan on articletreestartnodecte articletre9_ (cost=0.00..2.00 rows=100 width=16) (actual time=12,785.153..12,786.727 rows=100 loops=1)

48. 14.760 14.760 ↑ 1.0 1 180

Index Scan using pk_vendor_article on vendor_article children1_5_ (cost=0.42..0.52 rows=1 width=32) (actual time=0.080..0.082 rows=1 loops=180)

  • Index Cond: (children1_.id = id)
49. 1.260 1.260 ↑ 1.0 1 180

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.006..0.007 rows=1 loops=180)

  • Index Cond: (mainarticl0_.id = main_article_id)
50. 0.732 0.732 ↑ 1.0 1 183

Index Scan using pk_article_tree_node on article_tree_node articletre3_ (cost=0.42..0.57 rows=1 width=75) (actual time=0.003..0.004 rows=1 loops=183)

  • Index Cond: (mainarticl0_2_.parent_id = id)
Planning time : 2.566 ms
Execution time : 22,278.096 ms