explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XFLV

Settings
# exclusive inclusive rows x rows loops node
1. 419.929 30,940.692 ↓ 9.0 565 1

Hash Semi Join (cost=275,101.36..435,924.64 rows=63 width=90) (actual time=13,799.754..30,940.692 rows=565 loops=1)

  • Hash Cond: (mainarticl0_.id = mainarticl5_.article_tree_node_id)
2.          

CTE ancestorfiltercte

3. 0.997 35.726 ↓ 8.7 701 1

Recursive Union (cost=0.00..958.84 rows=81 width=16) (actual time=0.004..35.726 rows=701 loops=1)

4. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

5. 2.146 34.728 ↓ 29.1 233 3

Nested Loop (cost=0.85..95.72 rows=8 width=16) (actual time=9.405..11.576 rows=233 loops=3)

  • Join Filter: (ancestorfi0_.article_tree_node_id = children2_.parent_id)
6. 1.911 12.954 ↓ 23.4 234 3

Nested Loop (cost=0.42..84.72 rows=10 width=32) (actual time=0.012..4.318 rows=234 loops=3)

7. 0.528 0.528 ↓ 23.4 234 3

WorkTable Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.176 rows=234 loops=3)

8. 10.515 10.515 ↑ 1.0 1 701

Index Only Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..8.44 rows=1 width=16) (actual time=0.014..0.015 rows=1 loops=701)

  • Index Cond: (id = ancestorfi0_.article_tree_node_id)
  • Heap Fetches: 701
9. 19.628 19.628 ↑ 14.0 1 701

Index Scan using ix_article_tree_node_parent_id on article_tree_node children2_ (cost=0.42..0.92 rows=14 width=32) (actual time=0.022..0.028 rows=1 loops=701)

  • Index Cond: (parent_id = articletre1_.id)
  • Filter: (type <> 3)
  • Rows Removed by Filter: 1
10.          

CTE mainarticlestartnodecte

11. 0.521 64.730 ↓ 8.0 501 1

Limit (cost=688.36..688.52 rows=63 width=16) (actual time=54.595..64.730 rows=501 loops=1)

12. 10.470 64.209 ↓ 8.0 501 1

Sort (cost=688.36..688.52 rows=63 width=16) (actual time=54.592..64.209 rows=501 loops=1)

  • Sort Key: ancestorfi0__1.article_tree_node_id
  • Sort Method: quicksort Memory: 56kB
13. 10.751 53.739 ↓ 10.6 667 1

Nested Loop (cost=0.42..686.47 rows=63 width=16) (actual time=0.670..53.739 rows=667 loops=1)

14. 36.679 36.679 ↓ 8.7 701 1

CTE Scan on ancestorfiltercte ancestorfi0__1 (cost=0.00..1.62 rows=81 width=16) (actual time=0.006..36.679 rows=701 loops=1)

15. 6.309 6.309 ↑ 1.0 1 701

Index Scan using pk_article_tree_node on article_tree_node articletre1__1 (cost=0.42..8.45 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=701)

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

CTE hiddenbaseproductcte

17. 0.788 148.393 ↓ 1.7 168 1

Recursive Union (cost=2.41..1,481.58 rows=101 width=33) (actual time=4.310..148.393 rows=168 loops=1)

18. 1.418 145.667 ↓ 501.0 501 1

Nested Loop Left Join (cost=2.41..556.98 rows=1 width=33) (actual time=4.305..145.667 rows=501 loops=1)

19. 1.377 142.245 ↓ 501.0 501 1

Nested Loop (cost=1.98..556.40 rows=1 width=36) (actual time=4.274..142.245 rows=501 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_1_.id)
20. 1.464 103.794 ↓ 501.0 501 1

Nested Loop (cost=1.56..555.90 rows=1 width=84) (actual time=3.611..103.794 rows=501 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_.id)
21. 1.055 17.160 ↓ 501.0 501 1

Nested Loop (cost=1.13..555.42 rows=1 width=68) (actual time=1.745..17.160 rows=501 loops=1)

22. 1.166 14.101 ↓ 125.2 501 1

Nested Loop (cost=0.71..553.15 rows=4 width=68) (actual time=1.717..14.101 rows=501 loops=1)

23. 1.027 5.921 ↓ 8.0 501 1

Nested Loop (cost=0.42..533.77 rows=63 width=48) (actual time=0.098..5.921 rows=501 loops=1)

24. 0.385 0.385 ↓ 8.0 501 1

CTE Scan on mainarticlestartnodecte mainarticl0__1 (cost=0.00..1.26 rows=63 width=16) (actual time=0.001..0.385 rows=501 loops=1)

25. 4.509 4.509 ↑ 1.0 1 501

Index Scan using pk_article_tree_node on article_tree_node mainarticl1_2_ (cost=0.42..8.44 rows=1 width=32) (actual time=0.008..0.009 rows=1 loops=501)

  • Index Cond: (id = mainarticl0__1.article_tree_node_id)
26. 7.014 7.014 ↑ 1.0 1 501

Index Scan using pk_base_product on base_product articletre2_1_ (cost=0.28..0.30 rows=1 width=20) (actual time=0.013..0.014 rows=1 loops=501)

  • Index Cond: (id = mainarticl1_2_.parent_id)
27. 2.004 2.004 ↑ 1.0 1 501

Index Scan using pk_article_tree_node on article_tree_node articletre2_ (cost=0.42..0.56 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=501)

  • Index Cond: (id = mainarticl1_2_.parent_id)
28. 85.170 85.170 ↑ 1.0 1 501

Index Only Scan using pk_main_article on main_article mainarticl1_ (cost=0.42..0.47 rows=1 width=16) (actual time=0.170..0.170 rows=1 loops=501)

  • Index Cond: (id = mainarticl1_2_.id)
  • Heap Fetches: 501
29. 37.074 37.074 ↑ 1.0 1 501

Index Only Scan using pk_selectable_article on selectable_article mainarticl1_1_ (cost=0.42..0.48 rows=1 width=16) (actual time=0.073..0.074 rows=1 loops=501)

  • Index Cond: (id = mainarticl1_2_.id)
  • Heap Fetches: 501
30. 2.004 2.004 ↑ 1.0 1 501

Index Scan using pk_article_tree_node on article_tree_node articletre3__1 (cost=0.42..0.56 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=501)

  • Index Cond: (articletre2_.parent_id = id)
  • Filter: (type = 0)
31. 0.356 1.938 ↓ 2.3 23 6

Nested Loop Left Join (cost=0.99..92.26 rows=10 width=33) (actual time=0.030..0.323 rows=23 loops=6)

32. 0.266 1.302 ↓ 2.3 23 6

Nested Loop (cost=0.57..86.48 rows=10 width=36) (actual time=0.021..0.217 rows=23 loops=6)

  • Join Filter: (hiddenbase0_.parent_id = category1_.id)
33. 0.342 0.756 ↓ 2.3 23 6

Nested Loop (cost=0.42..84.72 rows=10 width=64) (actual time=0.013..0.126 rows=23 loops=6)

34. 0.078 0.078 ↓ 2.8 28 6

WorkTable Scan on hiddenbaseproductcte hiddenbase0_ (cost=0.00..0.20 rows=10 width=32) (actual time=0.001..0.013 rows=28 loops=6)

35. 0.336 0.336 ↑ 1.0 1 168

Index Scan using pk_article_tree_node on article_tree_node category1_1_ (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=168)

  • Index Cond: (id = hiddenbase0_.parent_id)
36. 0.280 0.280 ↑ 1.0 1 140

Index Scan using pk_category on category category1_ (cost=0.14..0.16 rows=1 width=20) (actual time=0.001..0.002 rows=1 loops=140)

  • Index Cond: (id = category1_1_.id)
37. 0.280 0.280 ↑ 1.0 1 140

Index Scan using pk_article_tree_node on article_tree_node articletre2__1 (cost=0.42..0.56 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=140)

  • Index Cond: (category1_1_.parent_id = id)
  • Filter: (type = 0)
38. 1,667.920 30,306.388 ↓ 1.1 783,863 1

Hash Join (cost=271,970.38..430,734.87 rows=729,075 width=129) (actual time=13,571.743..30,306.388 rows=783,863 loops=1)

  • Hash Cond: (mainarticl0_.id = mainarticl0_2_.id)
39. 1,239.931 24,615.041 ↓ 1.1 783,863 1

Hash Left Join (cost=147,653.97..267,165.68 rows=729,075 width=97) (actual time=8,924.160..24,615.041 rows=783,863 loops=1)

  • Hash Cond: (mainarticl0_.id = normalized2_.main_article_id)
40. 1,592.081 23,192.533 ↓ 1.1 779,565 1

Hash Join (cost=143,083.16..232,879.18 rows=729,075 width=76) (actual time=8,741.316..23,192.533 rows=779,565 loops=1)

  • Hash Cond: (mainarticl0_.id = mainarticl0_1_.id)
41. 1,111.985 20,393.967 ↓ 1.1 779,565 1

Hash Left Join (cost=110,478.66..170,508.91 rows=729,075 width=55) (actual time=4,666.920..20,393.967 rows=779,565 loops=1)

  • Hash Cond: (children1_.id = children1_5_.id)
42. 14,158.522 18,890.498 ↓ 1.1 779,565 1

Hash Right Join (cost=97,272.35..140,120.32 rows=729,075 width=39) (actual time=4,275.178..18,890.498 rows=779,565 loops=1)

  • Hash Cond: (children1_3_.id = children1_.id)
43. 650.113 650.113 ↑ 1.0 938,822 1

Seq Scan on selectable_article children1_3_ (cost=0.00..15,368.22 rows=938,822 width=19) (actual time=0.044..650.113 rows=938,822 loops=1)

44. 1,227.653 4,081.863 ↓ 1.1 779,565 1

Hash (cost=82,462.92..82,462.92 rows=729,075 width=36) (actual time=4,081.863..4,081.863 rows=779,565 loops=1)

  • Buckets: 65,536 (originally 65536) Batches: 32,768 (originally 16) Memory Usage: 27,223kB
45. 1,306.119 2,854.210 ↓ 1.1 779,565 1

Hash Right Join (cost=24,450.19..82,462.92 rows=729,075 width=36) (actual time=885.302..2,854.210 rows=779,565 loops=1)

  • Hash Cond: (children1_.parent_id = mainarticl0_.id)
46. 663.508 663.508 ↑ 1.0 942,607 1

Seq Scan on article_tree_node children1_ (cost=0.00..32,489.48 rows=942,648 width=36) (actual time=0.070..663.508 rows=942,607 loops=1)

47. 472.667 884.583 ↑ 1.0 729,075 1

Hash (cost=11,776.75..11,776.75 rows=729,075 width=16) (actual time=884.583..884.583 rows=729,075 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,163kB
48. 411.916 411.916 ↑ 1.0 729,075 1

Seq Scan on main_article mainarticl0_ (cost=0.00..11,776.75 rows=729,075 width=16) (actual time=0.015..411.916 rows=729,075 loops=1)

49. 157.051 391.484 ↑ 1.0 209,747 1

Hash (cost=9,150.47..9,150.47 rows=209,747 width=32) (actual time=391.483..391.484 rows=209,747 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,804kB
50. 234.433 234.433 ↑ 1.0 209,747 1

Seq Scan on vendor_article children1_5_ (cost=0.00..9,150.47 rows=209,747 width=32) (actual time=1.385..234.433 rows=209,747 loops=1)

51. 652.575 1,206.485 ↑ 1.0 938,822 1

Hash (cost=15,368.22..15,368.22 rows=938,822 width=21) (actual time=1,206.485..1,206.485 rows=938,822 loops=1)

  • Buckets: 65,536 Batches: 16 Memory Usage: 3,599kB
52. 553.910 553.910 ↑ 1.0 938,822 1

Seq Scan on selectable_article mainarticl0_1_ (cost=0.00..15,368.22 rows=938,822 width=21) (actual time=0.079..553.910 rows=938,822 loops=1)

53. 93.625 182.577 ↑ 1.0 117,325 1

Hash (cost=2,187.25..2,187.25 rows=117,325 width=37) (actual time=182.577..182.577 rows=117,325 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 2,535kB
54. 88.952 88.952 ↑ 1.0 117,325 1

Seq Scan on main_article_normalized_price normalized2_ (cost=0.00..2,187.25 rows=117,325 width=37) (actual time=0.948..88.952 rows=117,325 loops=1)

55. 708.219 4,023.427 ↑ 1.0 942,607 1

Hash (cost=106,089.31..106,089.31 rows=942,648 width=32) (actual time=4,023.426..4,023.427 rows=942,607 loops=1)

  • Buckets: 65,536 Batches: 32 Memory Usage: 2,066kB
56. 1,269.161 3,315.208 ↑ 1.0 942,607 1

Hash Left Join (cost=48,875.58..106,089.31 rows=942,648 width=32) (actual time=1,369.182..3,315.208 rows=942,607 loops=1)

  • Hash Cond: (mainarticl0_2_.parent_id = articletre3_.id)
57. 677.505 677.505 ↑ 1.0 942,607 1

Seq Scan on article_tree_node mainarticl0_2_ (cost=0.00..32,489.48 rows=942,648 width=32) (actual time=0.047..677.505 rows=942,607 loops=1)

58. 711.190 1,368.542 ↑ 1.0 942,607 1

Hash (cost=32,489.48..32,489.48 rows=942,648 width=16) (actual time=1,368.542..1,368.542 rows=942,607 loops=1)

  • Buckets: 131,072 Batches: 16 Memory Usage: 3,792kB
59. 657.352 657.352 ↑ 1.0 942,607 1

Seq Scan on article_tree_node articletre3_ (cost=0.00..32,489.48 rows=942,648 width=16) (actual time=0.009..657.352 rows=942,607 loops=1)

60. 0.479 65.826 ↓ 8.0 501 1

Hash (cost=1.26..1.26 rows=63 width=16) (actual time=65.825..65.826 rows=501 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 32kB
61. 65.347 65.347 ↓ 8.0 501 1

CTE Scan on mainarticlestartnodecte mainarticl5_ (cost=0.00..1.26 rows=63 width=16) (actual time=54.598..65.347 rows=501 loops=1)

62.          

SubPlan (for Hash Semi Join)

63. 0.000 0.000 ↓ 0.0 0

CTE Scan on hiddenbaseproductcte hiddenbase4_ (cost=0.00..2.27 rows=1 width=0) (never executed)

  • Filter: (hidden AND (article_tree_node_id = articletre3_.id))
64. 148.549 148.549 ↑ 1.8 28 1

CTE Scan on hiddenbaseproductcte hiddenbase4__1 (cost=0.00..2.02 rows=50 width=16) (actual time=146.367..148.549 rows=28 loops=1)

  • Filter: hidden
  • Rows Removed by Filter: 140
Planning time : 55.428 ms
Execution time : 30,941.892 ms