explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QJVd

Settings
# exclusive inclusive rows x rows loops node
1. 1.393 4,695.481 ↑ 1.8 197 1

Nested Loop Left Join (cost=64,149.68..98,004.75 rows=359 width=73) (actual time=3,369.170..4,695.481 rows=197 loops=1)

2.          

CTE mainarticlestartnodecte

3. 0.172 0.172 ↓ 1.0 200 1

Values Scan on "*VALUES*" (cost=0.00..2.50 rows=199 width=16) (actual time=0.003..0.172 rows=200 loops=1)

  • Filter: (column1 IS NOT NULL)
4.          

CTE hiddenbaseproductcte

5. 0.814 26.013 ↓ 1.5 303 1

Recursive Union (cost=2.39..3,529.10 rows=202 width=33) (actual time=0.104..26.013 rows=303 loops=1)

6. 0.522 9.421 ↓ 100.0 200 1

Nested Loop Left Join (cost=2.39..1,711.12 rows=2 width=33) (actual time=0.098..9.421 rows=200 loops=1)

7. 0.538 8.099 ↓ 100.0 200 1

Nested Loop (cost=1.97..1,709.94 rows=2 width=36) (actual time=0.079..8.099 rows=200 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_1_.id)
8. 0.448 6.161 ↓ 100.0 200 1

Nested Loop (cost=1.54..1,708.95 rows=2 width=84) (actual time=0.062..6.161 rows=200 loops=1)

  • Join Filter: (mainarticl0__1.article_tree_node_id = mainarticl1_.id)
9. 0.500 4.513 ↓ 100.0 200 1

Nested Loop (cost=1.12..1,708.01 rows=2 width=68) (actual time=0.041..4.513 rows=200 loops=1)

10. 0.516 3.213 ↓ 22.2 200 1

Nested Loop (cost=0.70..1,702.80 rows=9 width=68) (actual time=0.032..3.213 rows=200 loops=1)

11. 0.369 2.097 ↓ 1.0 200 1

Nested Loop (cost=0.42..1,641.53 rows=199 width=48) (actual time=0.022..2.097 rows=200 loops=1)

12. 0.128 0.128 ↓ 1.0 200 1

CTE Scan on mainarticlestartnodecte mainarticl0__1 (cost=0.00..3.98 rows=199 width=16) (actual time=0.001..0.128 rows=200 loops=1)

13. 1.600 1.600 ↑ 1.0 1 200

Index Scan using pk_article_tree_node on article_tree_node mainarticl1_2_ (cost=0.42..8.22 rows=1 width=32) (actual time=0.007..0.008 rows=1 loops=200)

  • Index Cond: (id = mainarticl0__1.article_tree_node_id)
14. 0.600 0.600 ↑ 1.0 1 200

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

  • Index Cond: (id = mainarticl1_2_.parent_id)
15. 0.800 0.800 ↑ 1.0 1 200

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

  • Index Cond: (id = mainarticl1_2_.parent_id)
16. 1.200 1.200 ↑ 1.0 1 200

Index Only Scan using pk_main_article on main_article mainarticl1_ (cost=0.42..0.46 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=200)

  • Index Cond: (id = mainarticl1_2_.id)
  • Heap Fetches: 200
17. 1.400 1.400 ↑ 1.0 1 200

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

  • Index Cond: (id = mainarticl1_2_.id)
  • Heap Fetches: 200
18. 0.800 0.800 ↑ 1.0 1 200

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

  • Index Cond: (id = articletre2_.parent_id)
  • Heap Fetches: 200
19. 3.997 15.778 ↓ 1.8 35 7

Nested Loop Left Join (cost=1.50..181.39 rows=20 width=33) (actual time=0.109..2.254 rows=35 loops=7)

20. 0.658 10.801 ↓ 1.8 35 7

Nested Loop (cost=1.07..169.61 rows=20 width=36) (actual time=0.102..1.543 rows=35 loops=7)

  • Join Filter: (hiddenbase0_.parent_id = category1_1_.id)
21. 3.257 4.508 ↓ 1.8 35 7

Hash Join (cost=0.65..6.05 rows=20 width=52) (actual time=0.091..0.644 rows=35 loops=7)

  • Hash Cond: (category1_.id = hiddenbase0_.parent_id)
22. 0.852 0.852 ↑ 1.0 233 6

Seq Scan on category category1_ (cost=0.00..4.33 rows=233 width=20) (actual time=0.008..0.142 rows=233 loops=6)

23. 0.210 0.399 ↓ 1.8 35 7

Hash (cost=0.40..0.40 rows=20 width=32) (actual time=0.057..0.057 rows=35 loops=7)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
24. 0.189 0.189 ↓ 2.1 43 7

WorkTable Scan on hiddenbaseproductcte hiddenbase0_ (cost=0.00..0.40 rows=20 width=32) (actual time=0.001..0.027 rows=43 loops=7)

25. 5.635 5.635 ↑ 1.0 1 245

Index Scan using pk_article_tree_node on article_tree_node category1_1_ (cost=0.42..8.17 rows=1 width=32) (actual time=0.022..0.023 rows=1 loops=245)

  • Index Cond: (id = category1_.id)
26. 0.980 0.980 ↑ 1.0 1 245

Index Only Scan using pk_article_tree_node on article_tree_node articletre2__1 (cost=0.42..0.57 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=245)

  • Index Cond: (id = category1_1_.parent_id)
  • Heap Fetches: 187
27. 0.760 4,666.160 ↑ 1.8 197 1

Nested Loop Left Join (cost=60,617.66..92,631.76 rows=359 width=74) (actual time=3,342.775..4,666.160 rows=197 loops=1)

28. 1.011 4,663.530 ↑ 1.9 187 1

Nested Loop Left Join (cost=60,617.24..92,458.93 rows=359 width=53) (actual time=3,342.754..4,663.530 rows=187 loops=1)

29. 216.104 4,658.405 ↑ 1.9 187 1

Hash Semi Join (cost=60,616.82..92,269.30 rows=359 width=53) (actual time=3,342.729..4,658.405 rows=187 loops=1)

  • Hash Cond: (mainarticl0_.id = mainarticl5_.article_tree_node_id)
30. 763.981 4,441.675 ↑ 1.2 278,990 1

Hash Right Join (cost=60,610.35..91,355.40 rows=344,168 width=85) (actual time=3,282.080..4,441.675 rows=278,990 loops=1)

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

32. 190.879 3,281.494 ↑ 1.0 190,631 1

Hash (cost=55,993.46..55,993.46 rows=190,631 width=69) (actual time=3,281.494..3,281.494 rows=190,631 loops=1)

  • Buckets: 65,536 Batches: 8 Memory Usage: 2,703kB
33. 685.770 3,090.615 ↑ 1.0 190,631 1

Hash Join (cost=27,527.79..55,993.46 rows=190,631 width=69) (actual time=2,000.845..3,090.615 rows=190,631 loops=1)

  • Hash Cond: (mainarticl0_2_.id = mainarticl0_.id)
34. 404.430 404.430 ↑ 1.0 448,681 1

Seq Scan on article_tree_node mainarticl0_2_ (cost=0.00..17,250.81 rows=448,681 width=32) (actual time=0.023..404.430 rows=448,681 loops=1)

35. 247.870 2,000.415 ↑ 1.0 190,631 1

Hash (cost=23,654.90..23,654.90 rows=190,631 width=37) (actual time=2,000.415..2,000.415 rows=190,631 loops=1)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,770kB
36. 861.798 1,752.545 ↑ 1.0 190,631 1

Hash Join (cost=6,647.20..23,654.90 rows=190,631 width=37) (actual time=500.138..1,752.545 rows=190,631 loops=1)

  • Hash Cond: (mainarticl0_1_.id = mainarticl0_.id)
37. 404.016 404.016 ↑ 1.0 445,047 1

Seq Scan on selectable_article mainarticl0_1_ (cost=0.00..7,285.47 rows=445,047 width=21) (actual time=0.016..404.016 rows=445,047 loops=1)

38. 269.561 486.731 ↑ 1.0 190,631 1

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

  • Buckets: 131,072 Batches: 4 Memory Usage: 3,264kB
39. 217.170 217.170 ↑ 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.031..217.170 rows=190,631 loops=1)

40. 0.157 0.626 ↓ 1.0 200 1

Hash (cost=3.98..3.98 rows=199 width=16) (actual time=0.625..0.626 rows=200 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
41. 0.469 0.469 ↓ 1.0 200 1

CTE Scan on mainarticlestartnodecte mainarticl5_ (cost=0.00..3.98 rows=199 width=16) (actual time=0.006..0.469 rows=200 loops=1)

42. 4.114 4.114 ↑ 1.0 1 187

Index Scan using pk_vendor_article on vendor_article children1_5_ (cost=0.42..0.52 rows=1 width=32) (actual time=0.021..0.022 rows=1 loops=187)

  • Index Cond: (children1_.id = id)
43. 1.870 1.870 ↑ 1.0 1 187

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.010..0.010 rows=1 loops=187)

  • Index Cond: (mainarticl0_.id = main_article_id)
44. 1.576 1.576 ↑ 1.0 1 197

Index Only Scan using pk_article_tree_node on article_tree_node articletre3_ (cost=0.42..0.57 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=197)

  • Index Cond: (id = mainarticl0_2_.parent_id)
  • Heap Fetches: 197
45.          

SubPlan (for Nested Loop Left Join)

46. 0.000 0.000 ↓ 0.0 0

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

  • Filter: (hidden AND (article_tree_node_id = articletre3_.id))
47. 26.352 26.352 ↑ 101.0 1 1

CTE Scan on hiddenbaseproductcte hiddenbase4__1 (cost=0.00..4.04 rows=101 width=16) (actual time=0.199..26.352 rows=1 loops=1)

  • Filter: hidden
  • Rows Removed by Filter: 302
Planning time : 8.217 ms
Execution time : 4,696.035 ms