explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N8Ic

Settings
# exclusive inclusive rows x rows loops node
1. 87.044 82,577.807 ↓ 1.2 502 1

Nested Loop Left Join (cost=275,929.97..19,906,555.09 rows=403 width=335) (actual time=563.773..82,577.807 rows=502 loops=1)

  • Join Filter: (articletre5_.origin_article_tree_node_id = mainarticl1_.id)
  • Rows Removed by Join Filter: 99396
2.          

CTE mainarticlestartnodecte

3. 0.469 0.469 ↓ 1.0 405 1

Values Scan on "*VALUES*" (cost=0.00..5.06 rows=403 width=16) (actual time=0.003..0.469 rows=405 loops=1)

  • Filter: (column1 IS NOT NULL)
4.          

CTE articletreenodecte

5. 6.178 451.805 ↑ 34.1 1,194 1

Recursive Union (cost=1.70..275,921.52 rows=40,703 width=89) (actual time=0.087..451.805 rows=1,194 loops=1)

6. 1.021 434.857 ↓ 1.0 405 1

Nested Loop Left Join (cost=1.70..3,904.21 rows=403 width=89) (actual time=0.075..434.857 rows=405 loops=1)

7. 1.457 430.596 ↓ 1.0 405 1

Nested Loop (cost=1.27..3,676.21 rows=403 width=69) (actual time=0.060..430.596 rows=405 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_2__1.id)
8. 1.400 344.089 ↓ 1.0 405 1

Nested Loop (cost=0.85..3,447.00 rows=403 width=48) (actual time=0.051..344.089 rows=405 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_1__1.id)
9. 0.958 217.544 ↓ 1.0 405 1

Nested Loop (cost=0.42..3,242.42 rows=403 width=32) (actual time=0.018..217.544 rows=405 loops=1)

10. 1.126 1.126 ↓ 1.0 405 1

CTE Scan on mainarticlestartnodecte mainarticl0__1 (cost=0.00..8.06 rows=403 width=16) (actual time=0.001..1.126 rows=405 loops=1)

11. 215.460 215.460 ↑ 1.0 1 405

Index Only Scan using pk_main_article on main_article mainarticl1__1 (cost=0.42..8.02 rows=1 width=16) (actual time=0.530..0.532 rows=1 loops=405)

  • Index Cond: (id = mainarticl0__1.article_tree_node_id)
  • Heap Fetches: 405
12. 125.145 125.145 ↑ 1.0 1 405

Index Only Scan using pk_selectable_article on selectable_article mainarticl1_1__1 (cost=0.42..0.50 rows=1 width=16) (actual time=0.308..0.309 rows=1 loops=405)

  • Index Cond: (id = mainarticl1__1.id)
  • Heap Fetches: 405
13. 85.050 85.050 ↑ 1.0 1 405

Index Scan using pk_article_tree_node on article_tree_node mainarticl1_2__1 (cost=0.42..0.56 rows=1 width=69) (actual time=0.209..0.210 rows=1 loops=405)

  • Index Cond: (id = mainarticl1_1__1.id)
14. 3.240 3.240 ↑ 1.0 1 405

Index Only Scan using pk_article_tree_node on article_tree_node articletre2_ (cost=0.42..0.56 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=405)

  • Index Cond: (id = mainarticl1_2__1.parent_id)
  • Heap Fetches: 405
15. 5.292 10.770 ↑ 24.3 166 6

Nested Loop (cost=0.42..27,120.32 rows=4,030 width=84) (actual time=0.082..1.795 rows=166 loops=6)

16. 0.702 0.702 ↑ 20.3 199 6

WorkTable Scan on articletreenodecte articletre0_ (cost=0.00..80.60 rows=4,030 width=68) (actual time=0.001..0.117 rows=199 loops=6)

17. 4.776 4.776 ↑ 1.0 1 1,194

Index Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..6.69 rows=1 width=69) (actual time=0.003..0.004 rows=1 loops=1,194)

  • Index Cond: (id = articletre0_.parent_id)
18. 2.694 777.713 ↓ 1.2 502 1

Nested Loop Left Join (cost=3.39..4,936.04 rows=403 width=241) (actual time=8.545..777.713 rows=502 loops=1)

19. 2.362 768.493 ↓ 1.2 502 1

Nested Loop Left Join (cost=2.96..4,708.03 rows=403 width=188) (actual time=8.232..768.493 rows=502 loops=1)

20. 2.320 672.257 ↓ 1.2 502 1

Nested Loop Left Join (cost=2.54..4,525.03 rows=403 width=167) (actual time=7.926..672.257 rows=502 loops=1)

21. 2.402 556.987 ↓ 1.2 502 1

Nested Loop Left Join (cost=2.12..4,331.24 rows=403 width=151) (actual time=7.600..556.987 rows=502 loops=1)

22. 2.179 344.749 ↓ 1.2 502 1

Nested Loop Left Join (cost=1.70..4,132.47 rows=403 width=148) (actual time=2.017..344.749 rows=502 loops=1)

23. 1.530 28.290 ↓ 1.0 405 1

Nested Loop Left Join (cost=1.27..3,688.38 rows=403 width=128) (actual time=1.679..28.290 rows=405 loops=1)

24. 1.514 21.090 ↓ 1.0 405 1

Nested Loop Left Join (cost=0.85..3,447.00 rows=403 width=17) (actual time=1.283..21.090 rows=405 loops=1)

25. 1.676 13.906 ↓ 1.0 405 1

Nested Loop (cost=0.42..3,243.43 rows=403 width=16) (actual time=0.865..13.906 rows=405 loops=1)

26. 0.485 0.485 ↓ 1.0 405 1

CTE Scan on mainarticlestartnodecte mainarticl0_ (cost=0.00..8.06 rows=403 width=16) (actual time=0.007..0.485 rows=405 loops=1)

27. 11.745 11.745 ↑ 1.0 1 405

Index Only Scan using pk_main_article on main_article mainarticl1_ (cost=0.42..8.02 rows=1 width=16) (actual time=0.027..0.029 rows=1 loops=405)

  • Index Cond: ((id = mainarticl0_.article_tree_node_id) AND (id IS NOT NULL))
  • Heap Fetches: 405
28. 5.670 5.670 ↑ 1.0 1 405

Index Scan using pk_selectable_article on selectable_article mainarticl1_1_ (cost=0.42..0.50 rows=1 width=17) (actual time=0.013..0.014 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = id)
29. 5.670 5.670 ↑ 1.0 1 405

Index Scan using pk_article_tree_node on article_tree_node mainarticl1_2_ (cost=0.42..0.59 rows=1 width=127) (actual time=0.012..0.014 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = id)
30. 314.280 314.280 ↑ 19.0 1 405

Index Scan using ix_article_tree_node_parent_id on article_tree_node children2_ (cost=0.42..0.91 rows=19 width=36) (actual time=0.768..0.776 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = parent_id)
31. 209.836 209.836 ↑ 1.0 1 502

Index Scan using pk_selectable_article on selectable_article children2_1_ (cost=0.42..0.48 rows=1 width=19) (actual time=0.414..0.418 rows=1 loops=502)

  • Index Cond: (children2_.id = id)
32. 112.950 112.950 ↑ 1.0 1 502

Index Scan using pk_vendor_article on vendor_article children2_2_ (cost=0.42..0.47 rows=1 width=32) (actual time=0.223..0.225 rows=1 loops=502)

  • Index Cond: (children2_.id = id)
33. 93.874 93.874 ↑ 1.0 1 502

Index Scan using main_article_normalized_price_pkey on main_article_normalized_price normalized3_ (cost=0.42..0.44 rows=1 width=37) (actual time=0.183..0.187 rows=1 loops=502)

  • Index Cond: (mainarticl1_.id = main_article_id)
34. 6.526 6.526 ↑ 1.0 1 502

Index Scan using pk_article_tree_node on article_tree_node articletre4_ (cost=0.42..0.56 rows=1 width=69) (actual time=0.009..0.013 rows=1 loops=502)

  • Index Cond: (mainarticl1_2_.parent_id = id)
35. 2,311.710 81,430.926 ↑ 102.3 199 502

CTE Scan on articletreenodecte articletre5_ (cost=0.00..18,649,554.93 rows=20,352 width=48) (actual time=119.225..162.213 rows=199 loops=502)

  • Filter: (SubPlan 5)
  • Rows Removed by Filter: 995
36.          

SubPlan (forCTE Scan)

37. 79,119.216 79,119.216 ↑ 51.0 4 599,388

CTE Scan on articletreenodecte articletre6_ (cost=0.00..915.82 rows=204 width=4) (actual time=0.016..0.132 rows=4 loops=599,388)

  • Filter: (origin_article_tree_node_id = articletre5_.origin_article_tree_node_id)
  • Rows Removed by Filter: 776
38.          

SubPlan (forNested Loop Left Join)

39. 9.538 137.046 ↑ 1.0 1 502

Aggregate (cost=1,018.08..1,018.09 rows=1 width=32) (actual time=0.272..0.273 rows=1 loops=502)

40. 127.508 127.508 ↑ 50.5 4 502

CTE Scan on articletreenodecte articletre7_ (cost=0.00..1,017.58 rows=202 width=16) (actual time=0.064..0.254 rows=4 loops=502)

  • Filter: ((parent_id <> articletre4_.id) AND (origin_article_tree_node_id = mainarticl1_.id))
  • Rows Removed by Filter: 1190
41. 8.032 145.078 ↑ 1.0 1 502

Aggregate (cost=946.65..946.66 rows=1 width=32) (actual time=0.288..0.289 rows=1 loops=502)

42. 12.787 137.046 ↑ 19.6 17 502

Hash Join (cost=18.49..945.81 rows=334 width=16) (actual time=0.093..0.273 rows=17 loops=502)

  • Hash Cond: (definedatt9_.attribute = articleatt10_.id)
43. 10.106 123.994 ↑ 19.6 17 502

Hash Join (cost=11.91..934.64 rows=334 width=16) (actual time=0.089..0.247 rows=17 loops=502)

  • Hash Cond: (articletre8_.article_tree_node_id = definedatt9_.node_id)
44. 113.452 113.452 ↑ 34.0 6 502

CTE Scan on articletreenodecte articletre8_ (cost=0.00..915.82 rows=204 width=16) (actual time=0.017..0.226 rows=6 loops=502)

  • Filter: (origin_article_tree_node_id = mainarticl1_.id)
  • Rows Removed by Filter: 1188
45. 0.244 0.436 ↑ 1.0 396 1

Hash (cost=6.96..6.96 rows=396 width=32) (actual time=0.436..0.436 rows=396 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
46. 0.192 0.192 ↑ 1.0 396 1

Seq Scan on article_tree_node_attribute definedatt9_ (cost=0.00..6.96 rows=396 width=32) (actual time=0.008..0.192 rows=396 loops=1)

47. 0.146 0.265 ↑ 1.0 159 1

Hash (cost=4.59..4.59 rows=159 width=16) (actual time=0.265..0.265 rows=159 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
48. 0.119 0.119 ↑ 1.0 159 1

Seq Scan on article_attribute articleatt10_ (cost=0.00..4.59 rows=159 width=16) (actual time=0.015..0.119 rows=159 loops=1)

Planning time : 9.807 ms
Execution time : 82,579.514 ms