explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gtrR

Settings
# exclusive inclusive rows x rows loops node
1. 8.864 1,064.679 ↓ 1.2 502 1

Nested Loop Left Join (cost=275,929.97..7,516,884,308.49 rows=403 width=335) (actual time=57.154..1,064.679 rows=502 loops=1)

2.          

CTE mainarticlestartnodecte

3. 0.383 0.383 ↓ 1.0 405 1

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

  • Filter: (column1 IS NOT NULL)
4.          

CTE articletreenodecte

5. 7.304 46.100 ↑ 34.1 1,194 1

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

6. 0.920 17.178 ↓ 1.0 405 1

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

7. 1.065 14.638 ↓ 1.0 405 1

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

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

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

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

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

10. 0.887 0.887 ↓ 1.0 405 1

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

11. 4.860 4.860 ↑ 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.011..0.012 rows=1 loops=405)

  • Index Cond: (id = mainarticl0__1.article_tree_node_id)
  • Heap Fetches: 405
12. 3.240 3.240 ↑ 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.007..0.008 rows=1 loops=405)

  • Index Cond: (id = mainarticl1__1.id)
  • Heap Fetches: 405
13. 2.835 2.835 ↑ 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.006..0.007 rows=1 loops=405)

  • Index Cond: (id = mainarticl1_1__1.id)
14. 1.620 1.620 ↑ 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.003..0.004 rows=1 loops=405)

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

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

16. 0.894 0.894 ↑ 20.3 199 6

WorkTable Scan on articletreenodecte articletre0_ (cost=0.00..80.60 rows=4,030 width=68) (actual time=0.001..0.149 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.004..0.004 rows=1 loops=1,194)

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

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

19. 1.385 49.971 ↓ 1.2 502 1

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

20. 1.857 43.064 ↓ 1.2 502 1

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

21. 1.775 35.685 ↓ 1.2 502 1

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

22. 1.495 21.760 ↓ 1.0 405 1

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

23. 1.521 16.215 ↓ 1.0 405 1

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

24. 1.640 10.644 ↓ 1.0 405 1

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

25. 0.499 0.499 ↓ 1.0 405 1

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

26. 8.505 8.505 ↑ 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.020..0.021 rows=1 loops=405)

  • Index Cond: ((id = mainarticl0_.article_tree_node_id) AND (id IS NOT NULL))
  • Heap Fetches: 405
27. 4.050 4.050 ↑ 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.009..0.010 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = id)
28. 4.050 4.050 ↑ 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.009..0.010 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = id)
29. 12.150 12.150 ↑ 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.011..0.030 rows=1 loops=405)

  • Index Cond: (mainarticl1_.id = parent_id)
30. 5.522 5.522 ↑ 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.010..0.011 rows=1 loops=502)

  • Index Cond: (children2_.id = id)
31. 5.522 5.522 ↑ 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.009..0.011 rows=1 loops=502)

  • Index Cond: (children2_.id = id)
32. 5.020 5.020 ↑ 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.008..0.010 rows=1 loops=502)

  • Index Cond: (mainarticl1_.id = main_article_id)
33. 3.514 3.514 ↑ 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.005..0.007 rows=1 loops=502)

  • Index Cond: (mainarticl1_2_.parent_id = id)
34.          

SubPlan (forNested Loop Left Join)

35. 8.032 219.374 ↑ 1.0 1 502

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

36. 211.342 211.342 ↑ 50.5 4 502

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

  • Filter: ((parent_id <> articletre4_.id) AND (origin_article_tree_node_id = mainarticl1_.id))
  • Rows Removed by Filter: 1190
37. 17.570 168.170 ↑ 1.0 1 502

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

38. 12.278 150.600 ↑ 19.6 17 502

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

  • Hash Cond: (definedatt9_.attribute = articleatt10_.id)
39. 9.472 138.050 ↑ 19.6 17 502

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

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

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

  • Filter: (origin_article_tree_node_id = mainarticl1_.id)
  • Rows Removed by Filter: 1188
41. 0.299 0.568 ↑ 1.0 396 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 33kB
42. 0.269 0.269 ↑ 1.0 396 1

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

43. 0.115 0.272 ↑ 1.0 159 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
44. 0.157 0.157 ↑ 1.0 159 1

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

45. 120.480 608.424 ↑ 102.0 1 502

CTE Scan on articletreenodecte articletre8__1 (cost=0.00..18,649,656.69 rows=102 width=32) (actual time=1.192..1.212 rows=1 loops=502)

  • Filter: ((origin_article_tree_node_id = mainarticl1_.id) AND (SubPlan 5))
  • Rows Removed by Filter: 1193
46.          

SubPlan (forCTE Scan)

47. 487.944 487.944 ↑ 51.0 4 3,012

CTE Scan on articletreenodecte articletre6_ (cost=0.00..915.82 rows=204 width=4) (actual time=0.014..0.162 rows=4 loops=3,012)

  • Filter: (origin_article_tree_node_id = articletre8__1.origin_article_tree_node_id)
  • Rows Removed by Filter: 771
Planning time : 8.994 ms
Execution time : 1,065.802 ms