explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4mOnZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.459 17,064.875 ↓ 1.0 183 1

Nested Loop Left Join (cost=634,702.17..1,783,477.34 rows=180 width=267) (actual time=17,046.315..17,064.875 rows=183 loops=1)

2.          

CTE ancestorfiltercte

3. 12.126 9,615.037 ↑ 2,514.9 105 1

Recursive Union (cost=11.21..451,070.86 rows=264,063 width=16) (actual time=6,955.197..9,615.037 rows=105 loops=1)

4. 863.194 7,771.923 ↑ 973.0 1 1

Hash Left Join (cost=11.21..22,556.33 rows=973 width=16) (actual time=6,955.195..7,771.923 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. 492.719 492.719 ↑ 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.021..492.719 rows=194,265 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
7. 0.269 0.269 ↑ 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.269 rows=365 loops=1)

8.          

SubPlan (for Hash Left Join)

9. 6,415.464 6,415.464 ↓ 0.0 0 194,408

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

  • Filter: (id = definedatt1_.attribute)
  • Rows Removed by Filter: 148
10. 76.804 1,830.988 ↑ 505.9 52 2

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

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

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

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

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

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

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

  • Sort Key: rec.article_tree_node_id
  • Sort Method: quicksort Memory: 25kB
14. 0.010 0.010 ↑ 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.005 rows=2 loops=2)

15.          

CTE articletreestartnodecte

16. 0.097 9,639.676 ↑ 1.0 100 1

Limit (cost=0.42..158.93 rows=100 width=16) (actual time=9,627.882..9,639.676 rows=100 loops=1)

17. 10.251 9,639.579 ↑ 1,124.0 100 1

Nested Loop (cost=0.42..178,169.61 rows=112,403 width=16) (actual time=9,627.880..9,639.579 rows=100 loops=1)

18. 9,615.153 9,615.153 ↑ 2,514.9 105 1

CTE Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..5,281.26 rows=264,063 width=16) (actual time=6,955.199..9,615.153 rows=105 loops=1)

19. 14.175 14.175 ↑ 1.0 1 105

Index Scan using ix_article_tree_node_no_vendor_articles on article_tree_node articletre1_ (cost=0.42..0.64 rows=1 width=16) (actual time=0.134..0.135 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.546 3.120 ↑ 33.7 300 1

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

22. 0.288 1.257 ↑ 1.0 100 1

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

23. 0.069 0.069 ↑ 1.0 100 1

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

24. 0.900 0.900 ↑ 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.009..0.009 rows=1 loops=100)

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

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

26. 0.156 0.156 ↑ 10.0 100 3

WorkTable Scan on articletreenodecte articletre0__2 (cost=0.00..20.00 rows=1,000 width=68) (actual time=0.001..0.052 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.357 17,063.867 ↓ 1.0 183 1

Nested Loop Left Join (cost=108,120.95..1,256,791.84 rows=180 width=204) (actual time=17,046.307..17,063.867 rows=183 loops=1)

29. 0.470 17,062.430 ↑ 1.0 180 1

Nested Loop Left Join (cost=108,120.54..1,256,705.18 rows=180 width=183) (actual time=17,046.293..17,062.430 rows=180 loops=1)

30. 10.251 17,059.980 ↑ 1.0 180 1

Hash Right Join (cost=108,120.12..1,256,610.10 rows=180 width=183) (actual time=17,046.276..17,059.980 rows=180 loops=1)

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

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

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

SubPlan (for CTE Scan)

33. 10.500 10.500 ↑ 16.7 3 300

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

  • Filter: (origin_article_tree_node_id = articletre4_.origin_article_tree_node_id)
  • Rows Removed by Filter: 231
34. 0.416 17,038.580 ↑ 1.0 180 1

Hash (cost=108,117.87..108,117.87 rows=180 width=151) (actual time=17,038.579..17,038.580 rows=180 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
35. 297.944 17,038.164 ↑ 1.0 180 1

Hash Semi Join (cost=74,239.60..108,117.87 rows=180 width=151) (actual time=14,795.389..17,038.164 rows=180 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre9_.article_tree_node_id)
36. 1,428.680 7,100.339 ↑ 1.2 278,990 1

Hash Right Join (cost=74,236.35..107,210.47 rows=343,675 width=183) (actual time=5,019.494..7,100.339 rows=278,990 loops=1)

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

38. 377.518 5,019.219 ↑ 1.0 190,631 1

Hash (cost=67,385.46..67,385.46 rows=190,631 width=167) (actual time=5,019.218..5,019.219 rows=190,631 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2433kB
39. 1,393.530 4,641.701 ↑ 1.0 190,631 1

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

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

41. 255.888 2,518.444 ↑ 1.0 190,631 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3630kB
42. 1,180.285 2,262.556 ↑ 1.0 190,631 1

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

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

44. 227.473 509.834 ↑ 1.0 190,631 1

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

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

46. 0.078 9,639.881 ↑ 1.0 100 1

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

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

CTE Scan on articletreestartnodecte articletre9_ (cost=0.00..2.00 rows=100 width=16) (actual time=9,627.884..9,639.803 rows=100 loops=1)

48. 1.980 1.980 ↑ 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.011..0.011 rows=1 loops=180)

  • Index Cond: (children1_.id = id)
49. 1.080 1.080 ↑ 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.005..0.006 rows=1 loops=180)

  • Index Cond: (mainarticl0_.id = main_article_id)
50. 0.549 0.549 ↑ 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.002..0.003 rows=1 loops=183)

  • Index Cond: (mainarticl0_2_.parent_id = id)
Planning time : 8.347 ms
Execution time : 17,078.278 ms