explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MTgb

Settings
# exclusive inclusive rows x rows loops node
1. 1.947 8,967.760 ↑ 1.0 37 1

Sort (cost=244,535.90..244,536.00 rows=38 width=360) (actual time=8,967.401..8,967.760 rows=37 loops=1)

  • Sort Key: vendorarti5_.special_offer_start_date, vendorarti5_.special_offer_end_date, vendorglob9_.name
  • Sort Method: quicksort Memory: 44kB
2.          

CTE articletreestartnodecte

3. 0.030 0.030 ↑ 1.0 20 1

Values Scan on "*VALUES*" (cost=0.00..0.25 rows=20 width=16) (actual time=0.005..0.030 rows=20 loops=1)

  • Filter: (column1 IS NOT NULL)
4.          

CTE articletreenodecte

5. 0.371 1.915 ↑ 14.4 140 1

Recursive Union (cost=0.42..16,599.80 rows=2,020 width=125) (actual time=0.089..1.915 rows=140 loops=1)

6. 0.084 0.445 ↑ 1.0 20 1

Nested Loop (cost=0.42..169.40 rows=20 width=125) (actual time=0.061..0.445 rows=20 loops=1)

7. 0.021 0.021 ↑ 1.0 20 1

CTE Scan on articletreestartnodecte articletre0_ (cost=0.00..0.40 rows=20 width=16) (actual time=0.002..0.021 rows=20 loops=1)

8. 0.340 0.340 ↑ 1.0 1 20

Index Scan using pk_article_tree_node on article_tree_node articletre1_ (cost=0.42..8.44 rows=1 width=105) (actual time=0.016..0.017 rows=1 loops=20)

  • Index Cond: (id = articletre0_.article_tree_node_id)
9. 0.448 1.099 ↑ 11.8 17 7

Nested Loop (cost=0.42..1,639.00 rows=200 width=84) (actual time=0.018..0.157 rows=17 loops=7)

10. 0.091 0.091 ↑ 10.0 20 7

WorkTable Scan on articletreenodecte articletre0__1 (cost=0.00..4.00 rows=200 width=68) (actual time=0.001..0.013 rows=20 loops=7)

11. 0.560 0.560 ↑ 1.0 1 140

Index Scan using pk_article_tree_node on article_tree_node articletre1__1 (cost=0.42..8.16 rows=1 width=105) (actual time=0.003..0.004 rows=1 loops=140)

  • Index Cond: (id = articletre0__1.parent_id)
12. 0.636 8,965.813 ↑ 1.0 37 1

Hash Left Join (cost=177,004.69..227,934.86 rows=38 width=360) (actual time=8,228.719..8,965.813 rows=37 loops=1)

  • Hash Cond: (vendorkeyc8_.vendor_global_company_id = vendorglob9_.id)
13. 0.135 8,872.776 ↑ 1.0 37 1

Hash Left Join (cost=177,002.29..222,697.32 rows=38 width=350) (actual time=8,226.920..8,872.776 rows=37 loops=1)

  • Hash Cond: (vendorcomp7_.keycloak_account_id = vendorkeyc8_.id)
14. 0.179 8,872.551 ↑ 1.0 37 1

Hash Left Join (cost=176,999.76..222,694.32 rows=38 width=350) (actual time=8,226.825..8,872.551 rows=37 loops=1)

  • Hash Cond: (vendorarti5_.vendor_id = vendorcomp7_.id)
15. 183.539 8,872.232 ↑ 1.0 37 1

Hash Semi Join (cost=176,994.78..222,688.81 rows=38 width=350) (actual time=8,226.672..8,872.232 rows=37 loops=1)

  • Hash Cond: (mainarticl0_.id = articletre13_.article_tree_node_id)
16. 961.455 8,688.593 ↑ 1.4 218,280 1

Hash Right Join (cost=176,994.13..221,882.48 rows=306,766 width=366) (actual time=6,356.281..8,688.593 rows=218,280 loops=1)

  • Hash Cond: (atn.parent_id = mainarticl0_.id)
17. 945.913 1,905.663 ↑ 1.0 367,697 1

Hash Left Join (cost=13,432.59..39,003.76 rows=378,272 width=64) (actual time=533.038..1,905.663 rows=367,697 loops=1)

  • Hash Cond: (atn.id = vendorarti5_.id)
18. 427.185 427.185 ↑ 1.0 367,697 1

Seq Scan on article_tree_node atn (cost=0.00..14,893.72 rows=378,272 width=32) (actual time=0.017..427.185 rows=367,697 loops=1)

19. 247.805 532.565 ↑ 1.0 212,022 1

Hash (cost=8,801.93..8,801.93 rows=217,493 width=48) (actual time=532.564..532.565 rows=212,022 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2174kB
20. 284.760 284.760 ↑ 1.0 212,022 1

Seq Scan on vendor_article vendorarti5_ (cost=0.00..8,801.93 rows=217,493 width=48) (actual time=0.195..284.760 rows=212,022 loops=1)

21. 274.873 5,821.475 ↑ 1.0 152,041 1

Hash (cost=154,863.28..154,863.28 rows=159,621 width=318) (actual time=5,821.475..5,821.475 rows=152,041 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 2012kB
22. 524.492 5,546.602 ↑ 1.0 152,041 1

Hash Left Join (cost=98,161.12..154,863.28 rows=159,621 width=318) (actual time=3,312.814..5,546.602 rows=152,041 loops=1)

  • Hash Cond: (mainarticl0_.id = selectable1_.id)
23. 417.467 4,812.986 ↑ 1.0 152,041 1

Hash Left Join (cost=94,259.19..140,079.69 rows=159,621 width=191) (actual time=3,103.497..4,812.986 rows=152,041 loops=1)

  • Hash Cond: (mainarticl0_2_.parent_id = articletre2_.id)
24. 913.624 3,584.628 ↑ 1.0 152,041 1

Hash Join (cost=72,789.07..106,450.52 rows=159,621 width=191) (actual time=2,291.521..3,584.628 rows=152,041 loops=1)

  • Hash Cond: (mainarticl0_2_.id = mainarticl0_.id)
25. 384.085 384.085 ↑ 1.0 367,697 1

Seq Scan on article_tree_node mainarticl0_2_ (cost=0.00..14,893.72 rows=378,272 width=127) (actual time=0.056..384.085 rows=367,697 loops=1)

26. 220.269 2,286.919 ↑ 1.0 152,041 1

Hash (cost=69,078.81..69,078.81 rows=159,621 width=64) (actual time=2,286.918..2,286.919 rows=152,041 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 1702kB
27. 183.764 2,066.650 ↑ 1.0 152,041 1

Hash Right Join (cost=21,979.72..69,078.81 rows=159,621 width=64) (actual time=1,885.266..2,066.650 rows=152,041 loops=1)

  • Hash Cond: (articletre3_.origin_article_tree_node_id = mainarticl0_.id)
28. 0.576 7.436 ↑ 50.5 20 1

CTE Scan on articletreenodecte articletre3_ (cost=0.00..45,975.20 rows=1,010 width=48) (actual time=3.889..7.436 rows=20 loops=1)

  • Filter: (SubPlan 5)
  • Rows Removed by Filter: 120
29.          

SubPlan (forCTE Scan)

30. 6.860 6.860 ↑ 2.0 5 140

CTE Scan on articletreenodecte articletre4_ (cost=0.00..45.45 rows=10 width=4) (actual time=0.002..0.049 rows=5 loops=140)

  • Filter: (origin_article_tree_node_id = articletre3_.origin_article_tree_node_id)
  • Rows Removed by Filter: 84
31. 204.779 1,875.450 ↑ 1.0 152,041 1

Hash (cost=18,892.46..18,892.46 rows=159,621 width=32) (actual time=1,875.449..1,875.450 rows=152,041 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2883kB
32. 751.078 1,670.671 ↑ 1.0 152,041 1

Hash Join (cost=5,633.47..18,892.46 rows=159,621 width=32) (actual time=528.284..1,670.671 rows=152,041 loops=1)

  • Hash Cond: (mainarticl0_1_.id = mainarticl0_.id)
33. 392.191 392.191 ↑ 1.0 364,063 1

Seq Scan on selectable_article mainarticl0_1_ (cost=0.00..5,961.29 rows=364,129 width=16) (actual time=0.037..392.191 rows=364,063 loops=1)

34. 322.167 527.402 ↑ 1.0 152,041 1

Hash (cost=2,858.21..2,858.21 rows=159,621 width=16) (actual time=527.401..527.402 rows=152,041 loops=1)

  • Buckets: 131072 Batches: 4 Memory Usage: 2803kB
35. 205.235 205.235 ↑ 1.0 152,041 1

Seq Scan on main_article mainarticl0_ (cost=0.00..2,858.21 rows=159,621 width=16) (actual time=0.032..205.235 rows=152,041 loops=1)

36. 400.128 810.891 ↑ 1.0 367,697 1

Hash (cost=14,893.72..14,893.72 rows=378,272 width=16) (actual time=810.890..810.891 rows=367,697 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3196kB
37. 410.763 410.763 ↑ 1.0 367,697 1

Seq Scan on article_tree_node articletre2_ (cost=0.00..14,893.72 rows=378,272 width=16) (actual time=0.010..410.763 rows=367,697 loops=1)

38. 125.228 209.124 ↑ 1.0 60,992 1

Hash (cost=1,884.08..1,884.08 rows=61,108 width=143) (actual time=209.124..209.124 rows=60,992 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 2776kB
39. 83.896 83.896 ↑ 1.0 60,992 1

Seq Scan on selectable_article_lmiv selectable1_ (cost=0.00..1,884.08 rows=61,108 width=143) (actual time=0.009..83.896 rows=60,992 loops=1)

40. 0.026 0.100 ↑ 1.0 20 1

Hash (cost=0.40..0.40 rows=20 width=16) (actual time=0.099..0.100 rows=20 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
41. 0.074 0.074 ↑ 1.0 20 1

CTE Scan on articletreestartnodecte articletre13_ (cost=0.00..0.40 rows=20 width=16) (actual time=0.010..0.074 rows=20 loops=1)

42. 0.058 0.140 ↑ 1.0 88 1

Hash (cost=3.88..3.88 rows=88 width=32) (actual time=0.139..0.140 rows=88 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
43. 0.082 0.082 ↑ 1.0 88 1

Seq Scan on vendor_company vendorcomp7_ (cost=0.00..3.88 rows=88 width=32) (actual time=0.012..0.082 rows=88 loops=1)

44. 0.045 0.090 ↑ 1.0 68 1

Hash (cost=1.68..1.68 rows=68 width=32) (actual time=0.089..0.090 rows=68 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
45. 0.045 0.045 ↑ 1.0 68 1

Seq Scan on vendor_keycloak_account vendorkeyc8_ (cost=0.00..1.68 rows=68 width=32) (actual time=0.005..0.045 rows=68 loops=1)

46. 0.055 0.123 ↓ 1.0 63 1

Hash (cost=1.62..1.62 rows=62 width=40) (actual time=0.122..0.123 rows=63 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
47. 0.068 0.068 ↓ 1.0 63 1

Seq Scan on vendor_global_company vendorglob9_ (cost=0.00..1.62 rows=62 width=40) (actual time=0.005..0.068 rows=63 loops=1)

48.          

SubPlan (forHash Left Join)

49. 0.666 29.748 ↑ 1.0 1 37

Aggregate (cost=57.43..57.44 rows=1 width=32) (actual time=0.803..0.804 rows=1 loops=37)

50. 1.073 29.082 ↑ 2.0 8 37

Nested Loop (cost=45.72..57.39 rows=16 width=16) (actual time=0.097..0.786 rows=8 loops=37)

51. 11.840 26.529 ↑ 2.0 8 37

Hash Join (cost=45.57..53.75 rows=16 width=16) (actual time=0.077..0.717 rows=8 loops=37)

  • Hash Cond: (definedatt11_.node_id = articletre10_.article_tree_node_id)
52. 12.580 12.580 ↑ 1.0 365 37

Seq Scan on article_tree_node_attribute definedatt11_ (cost=0.00..6.65 rows=365 width=32) (actual time=0.010..0.340 rows=365 loops=37)

53. 0.666 2.109 ↑ 1.4 7 37

Hash (cost=45.45..45.45 rows=10 width=16) (actual time=0.054..0.057 rows=7 loops=37)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 1.443 1.443 ↑ 1.4 7 37

CTE Scan on articletreenodecte articletre10_ (cost=0.00..45.45 rows=10 width=16) (actual time=0.005..0.039 rows=7 loops=37)

  • Filter: (origin_article_tree_node_id = mainarticl0_.id)
  • Rows Removed by Filter: 133
55. 1.480 1.480 ↑ 1.0 1 296

Index Only Scan using pk_article_attribute on article_attribute articleatt12_ (cost=0.14..0.22 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=296)

  • Index Cond: (id = definedatt11_.attribute)
  • Heap Fetches: 296
56. 0.074 62.530 ↓ 0.0 0 37

Nested Loop (cost=1.27..80.31 rows=1 width=16) (actual time=1.690..1.690 rows=0 loops=37)

57. 0.261 62.456 ↓ 0.0 0 37

Nested Loop (cost=0.84..79.24 rows=1 width=32) (actual time=1.688..1.688 rows=0 loops=37)

58. 58.793 58.793 ↑ 3.0 2 37

Index Only Scan using ix_article_tree_node_parent_id on article_tree_node atn_1 (cost=0.42..28.50 rows=6 width=16) (actual time=1.570..1.589 rows=2 loops=37)

  • Index Cond: (parent_id = mainarticl0_2_.id)
  • Heap Fetches: 84
59. 3.402 3.402 ↓ 0.0 0 81

Index Scan using pk_vendor_article on vendor_article va (cost=0.42..8.45 rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=81)

  • Index Cond: (id = atn_1.id)
  • Filter: ((special_offer_start_date <= now()) AND (special_offer_end_date >= now()))
  • Rows Removed by Filter: 1
60. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_selectable_article on selectable_article sa (cost=0.42..1.05 rows=1 width=16) (never executed)

  • Index Cond: (id = va.id)
  • Filter: ((NOT price_calculation_failed) AND ((backoffice_status = 1) OR ((backoffice_status = 0) AND is_active_in_necta)))
Planning time : 31.957 ms
Execution time : 8,969.211 ms