explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2ziM

Settings
# exclusive inclusive rows x rows loops node
1. 35.143 31,578.268 ↓ 45.3 9,387 1

Nested Loop Left Join (cost=198,426.18..1,717,597.13 rows=207 width=267) (actual time=20,344.672..31,578.268 rows=9,387 loops=1)

2.          

CTE ancestorfiltercte

3. 8.015 66.206 ↓ 18.6 5,029 1

Recursive Union (cost=0.00..2,834.78 rows=271 width=16) (actual time=0.046..66.206 rows=5,029 loops=1)

4. 0.231 0.231 ↑ 1.0 1 1

Seq Scan on article_tree_node_attribute definedatt1_ (cost=0.00..7.56 rows=1 width=16) (actual time=0.042..0.231 rows=1 loops=1)

  • Filter: (attribute = 'a229bde9-9c5b-0000-0000-000000000000'::uuid)
  • Rows Removed by Filter: 364
5. 16.671 57.960 ↓ 93.1 2,514 2

Nested Loop (cost=4.47..282.18 rows=27 width=16) (actual time=0.064..28.980 rows=2,514 loops=2)

6. 0.004 0.004 ↑ 5.0 2 2

WorkTable Scan on ancestorfiltercte rec (cost=0.00..0.20 rows=10 width=16) (actual time=0.002..0.002 rows=2 loops=2)

7. 37.745 41.285 ↓ 335.3 1,006 5

Bitmap Heap Scan on article_tree_node (cost=4.47..28.17 rows=3 width=32) (actual time=1.104..8.257 rows=1,006 loops=5)

  • Recheck Cond: (parent_id = rec.article_tree_node_id)
  • Filter: (type <> 3)
  • Heap Blocks: exact=2397
8. 3.540 3.540 ↓ 591.2 3,547 5

Bitmap Index Scan on ix_article_tree_node_parent_id (cost=0.00..4.47 rows=6 width=0) (actual time=0.708..0.708 rows=3,547 loops=5)

  • Index Cond: (parent_id = rec.article_tree_node_id)
9.          

CTE articletreestartnodecte

10. 15.734 260.502 ↓ 43.5 5,000 1

Limit (cost=0.42..2,212.05 rows=115 width=16) (actual time=6.463..260.502 rows=5,000 loops=1)

11. 14.447 244.768 ↓ 43.5 5,000 1

Nested Loop (cost=0.42..2,212.05 rows=115 width=16) (actual time=6.460..244.768 rows=5,000 loops=1)

12. 84.480 84.480 ↓ 18.6 5,029 1

CTE Scan on ancestorfiltercte ancestorfi0_ (cost=0.00..5.42 rows=271 width=16) (actual time=0.049..84.480 rows=5,029 loops=1)

13. 145.841 145.841 ↑ 1.0 1 5,029

Index Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..8.13 rows=1 width=16) (actual time=0.026..0.029 rows=1 loops=5,029)

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

CTE articletreenodecte

15. 59.585 351.356 ↓ 1.3 15,000 1

Recursive Union (cost=0.42..85,256.85 rows=11,615 width=113) (actual time=0.071..351.356 rows=15,000 loops=1)

16. 11.747 60.123 ↓ 43.5 5,000 1

Nested Loop (cost=0.42..962.05 rows=115 width=113) (actual time=0.063..60.123 rows=5,000 loops=1)

17. 3.376 3.376 ↓ 43.5 5,000 1

CTE Scan on articletreestartnodecte articletre0_ (cost=0.00..2.30 rows=115 width=16) (actual time=0.002..3.376 rows=5,000 loops=1)

18. 45.000 45.000 ↑ 1.0 1 5,000

Index Scan using pk_article_tree_node on article_tree_node articletre1__1 (cost=0.42..8.34 rows=1 width=93) (actual time=0.008..0.009 rows=1 loops=5,000)

  • Index Cond: (id = articletre0_.article_tree_node_id)
19. 68.763 231.648 ↓ 2.9 3,333 3

Nested Loop (cost=0.42..8,406.25 rows=1,150 width=84) (actual time=3.590..77.216 rows=3,333 loops=3)

20. 12.885 12.885 ↓ 4.3 5,000 3

WorkTable Scan on articletreenodecte articletre0__1 (cost=0.00..23.00 rows=1,150 width=68) (actual time=0.001..4.295 rows=5,000 loops=3)

21. 150.000 150.000 ↑ 1.0 1 15,000

Index Scan using pk_article_tree_node on article_tree_node articletre1__2 (cost=0.42..7.27 rows=1 width=93) (actual time=0.008..0.010 rows=1 loops=15,000)

  • Index Cond: (id = articletre0__1.parent_id)
22. 24.270 31,496.190 ↓ 45.3 9,387 1

Nested Loop Left Join (cost=108,122.08..1,627,173.04 rows=207 width=204) (actual time=20,344.662..31,496.190 rows=9,387 loops=1)

23. 35.151 31,286.780 ↓ 44.7 9,257 1

Nested Loop Left Join (cost=108,121.66..1,627,073.38 rows=207 width=183) (actual time=20,344.644..31,286.780 rows=9,257 loops=1)

24. 29.388 31,066.489 ↓ 44.7 9,257 1

Hash Right Join (cost=108,121.24..1,626,964.04 rows=207 width=183) (actual time=20,344.560..31,066.489 rows=9,257 loops=1)

  • Hash Cond: (articletre4_.origin_article_tree_node_id = mainarticl0_.id)
25. 77.193 26,462.193 ↑ 1.2 5,000 1

CTE Scan on articletreenodecte articletre4_ (cost=0.00..1,518,820.96 rows=5,808 width=48) (actual time=15,769.633..26,462.193 rows=5,000 loops=1)

  • Filter: (SubPlan 4)
  • Rows Removed by Filter: 10000
26.          

SubPlan (for CTE Scan)

27. 26,385.000 26,385.000 ↑ 19.3 3 15,000

CTE Scan on articletreenodecte articletre5_ (cost=0.00..261.34 rows=58 width=4) (actual time=0.379..1.759 rows=3 loops=15,000)

  • Filter: (origin_article_tree_node_id = articletre4_.origin_article_tree_node_id)
  • Rows Removed by Filter: 11664
28. 10.032 4,574.908 ↓ 44.7 9,257 1

Hash (cost=108,118.65..108,118.65 rows=207 width=151) (actual time=4,574.908..4,574.908 rows=9,257 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1413kB
29. 201.322 4,564.876 ↓ 44.7 9,257 1

Hash Semi Join (cost=74,240.09..108,118.65 rows=207 width=151) (actual time=3,306.215..4,564.876 rows=9,257 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre9_.article_tree_node_id)
30. 707.926 4,075.384 ↑ 1.2 278,990 1

Hash Right Join (cost=74,236.35..107,210.47 rows=343,675 width=183) (actual time=3,017.605..4,075.384 rows=278,990 loops=1)

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

32. 235.362 3,017.320 ↑ 1.0 190,631 1

Hash (cost=67,385.46..67,385.46 rows=190,631 width=167) (actual time=3,017.320..3,017.320 rows=190,631 loops=1)

  • Buckets: 32768 Batches: 16 Memory Usage: 2433kB
33. 784.019 2,781.958 ↑ 1.0 190,631 1

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

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

35. 158.867 1,587.747 ↑ 1.0 190,631 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3630kB
36. 644.464 1,428.880 ↑ 1.0 190,631 1

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

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

38. 259.762 449.704 ↑ 1.0 190,631 1

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

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

40. 5.517 288.170 ↓ 43.5 5,000 1

Hash (cost=2.30..2.30 rows=115 width=16) (actual time=288.170..288.170 rows=5,000 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 299kB
41. 282.653 282.653 ↓ 43.5 5,000 1

CTE Scan on articletreestartnodecte articletre9_ (cost=0.00..2.30 rows=115 width=16) (actual time=6.467..282.653 rows=5,000 loops=1)

42. 185.140 185.140 ↑ 1.0 1 9,257

Index Scan using pk_vendor_article on vendor_article children1_5_ (cost=0.42..0.52 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=9,257)

  • Index Cond: (children1_.id = id)
43. 185.140 185.140 ↑ 1.0 1 9,257

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.019..0.020 rows=1 loops=9,257)

  • Index Cond: (mainarticl0_.id = main_article_id)
44. 46.935 46.935 ↑ 1.0 1 9,387

Index Scan using pk_article_tree_node on article_tree_node articletre3_ (cost=0.42..0.57 rows=1 width=75) (actual time=0.004..0.005 rows=1 loops=9,387)

  • Index Cond: (mainarticl0_2_.parent_id = id)
Planning time : 3.383 ms
Execution time : 31,586.474 ms