explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A1bC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on mstr_3 (cost=72,404,391.68..72,479,795.91 rows=12,567,372 width=140) (actual rows= loops=)

2.          

CTE menu

3. 0.000 0.000 ↓ 0.0

WindowAgg (cost=6.75..8.33 rows=243 width=76) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Sort (cost=6.75..6.87 rows=243 width=36) (actual rows= loops=)

  • Sort Key: menus.id
5. 0.000 0.000 ↓ 0.0

Seq Scan on menus (cost=0.00..4.82 rows=243 width=36) (actual rows= loops=)

  • Filter: (end_date < (('now'::cstring)::date - (date_part('dow'::text, (('now'::cstring)::date)::timestamp without time zone))::integer))
6.          

CTE u

7. 0.000 0.000 ↓ 0.0

Index Only Scan using users_pkey on users (cost=0.08..17,031.80 rows=396,587 width=4) (actual rows= loops=)

8.          

CTE mi

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=109.63..110.20 rows=191 width=12) (actual rows= loops=)

  • Group Key: mi.menu_id
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=42.90..105.79 rows=3,836 width=8) (actual rows= loops=)

  • Hash Cond: (mi.product_id = p.id)
11. 0.000 0.000 ↓ 0.0

Seq Scan on menu_items mi (cost=0.00..48.51 rows=3,836 width=12) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=41.08..41.08 rows=520 width=4) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on products p (cost=0.00..41.08 rows=520 width=4) (actual rows= loops=)

  • Filter: (lower((name)::text) !~~ '%family%'::text)
14.          

CTE avoid_tag

15. 0.000 0.000 ↓ 0.0

Seq Scan on meal_preferences (cost=0.00..3,873.11 rows=74,402 width=8) (actual rows= loops=)

  • Filter: (preference = 0)
16.          

CTE master_menu_meal_tag

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=291.05..879,542.26 rows=259,202,579 width=16) (actual rows= loops=)

  • Hash Cond: (baskets.menu_id = menu_items.menu_id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on baskets (cost=0.00..35,842.39 rows=1,879,797 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=198.87..198.87 rows=26,337 width=12) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=54.09..198.87 rows=26,337 width=12) (actual rows= loops=)

  • Hash Cond: (menu_items.product_id = product_meal_tags.product_id)
21. 0.000 0.000 ↓ 0.0

Seq Scan on menu_items (cost=0.00..48.51 rows=3,836 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=43.27..43.27 rows=3,091 width=8) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on product_meal_tags (cost=0.00..43.27 rows=3,091 width=8) (actual rows= loops=)

24.          

CTE grp

25. 0.000 0.000 ↓ 0.0

Merge Join (cost=11,965,544.67..14,264,841.47 rows=482,129,757 width=16) (actual rows= loops=)

  • Merge Cond: ((avoid_tag.user_id = master_menu_meal_tag.user_id) AND (avoid_tag.meal_tag_id = master_menu_meal_tag.meal_tag_id))
26. 0.000 0.000 ↓ 0.0

Sort (cost=1,650.46..1,687.66 rows=74,402 width=8) (actual rows= loops=)

  • Sort Key: avoid_tag.user_id, avoid_tag.meal_tag_id
27. 0.000 0.000 ↓ 0.0

CTE Scan on avoid_tag (cost=0.00..446.41 rows=74,402 width=8) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Materialize (cost=11,963,894.21..12,223,096.79 rows=259,202,579 width=16) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=11,963,894.21..12,093,495.50 rows=259,202,579 width=16) (actual rows= loops=)

  • Sort Key: master_menu_meal_tag.user_id, master_menu_meal_tag.meal_tag_id
30. 0.000 0.000 ↓ 0.0

CTE Scan on master_menu_meal_tag (cost=0.00..1,555,215.47 rows=259,202,579 width=16) (actual rows= loops=)

31.          

CTE grp_2

32. 0.000 0.000 ↓ 0.0

Group (cost=28,570,487.25..29,534,746.76 rows=8,000,000 width=16) (actual rows= loops=)

  • Group Key: grp.user_id, grp.menu_id, grp.product_id
33. 0.000 0.000 ↓ 0.0

Sort (cost=28,570,487.25..28,811,552.13 rows=482,129,757 width=12) (actual rows= loops=)

  • Sort Key: grp.user_id, grp.menu_id, grp.product_id
34. 0.000 0.000 ↓ 0.0

CTE Scan on grp (cost=0.00..2,892,778.54 rows=482,129,757 width=12) (actual rows= loops=)

35.          

CTE mstr_grp

36. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=12,293,004.26..13,734,618.44 rows=259,202,579 width=16) (actual rows= loops=)

  • Merge Cond: ((grp_2.user_id = master_menu_meal_tag_1.user_id) AND (grp_2.menu_id = master_menu_meal_tag_1.menu_id) AND (grp_2.product_id = master_menu_meal_tag_1.product_id))
37. 0.000 0.000 ↓ 0.0

Sort (cost=329,110.05..333,110.05 rows=8,000,000 width=16) (actual rows= loops=)

  • Sort Key: grp_2.user_id, grp_2.menu_id, grp_2.product_id
38. 0.000 0.000 ↓ 0.0

CTE Scan on grp_2 (cost=0.00..48,000.00 rows=8,000,000 width=16) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=11,963,894.21..12,223,096.79 rows=259,202,579 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Sort (cost=11,963,894.21..12,093,495.50 rows=259,202,579 width=12) (actual rows= loops=)

  • Sort Key: master_menu_meal_tag_1.user_id, master_menu_meal_tag_1.menu_id, master_menu_meal_tag_1.product_id
41. 0.000 0.000 ↓ 0.0

CTE Scan on master_menu_meal_tag master_menu_meal_tag_1 (cost=0.00..1,555,215.47 rows=259,202,579 width=12) (actual rows= loops=)

42.          

CTE mstr_grp_2

43. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=11,963,894.21..12,643,900.66 rows=8,000,000 width=16) (actual rows= loops=)

  • Group Key: mstr_grp.user_id, mstr_grp.menu_id, mstr_grp.product_id
44. 0.000 0.000 ↓ 0.0

Sort (cost=11,963,894.21..12,093,495.50 rows=259,202,579 width=16) (actual rows= loops=)

  • Sort Key: mstr_grp.user_id, mstr_grp.menu_id, mstr_grp.product_id
45. 0.000 0.000 ↓ 0.0

CTE Scan on mstr_grp (cost=0.00..1,555,215.47 rows=259,202,579 width=16) (actual rows= loops=)

46.          

CTE non_eligible_grp

47. 0.000 0.000 ↓ 0.0

HashAggregate (cost=64,000.00..64,120.00 rows=40,000 width=24) (actual rows= loops=)

  • Group Key: mstr_grp_2.user_id, mstr_grp_2.menu_id
48. 0.000 0.000 ↓ 0.0

CTE Scan on mstr_grp_2 (cost=0.00..48,000.00 rows=8,000,000 width=12) (actual rows= loops=)

49.          

CTE mstr

50. 0.000 0.000 ↓ 0.0

Merge Join (cost=188,353.20..252,728.61 rows=12,567,372 width=92) (actual rows= loops=)

  • Merge Cond: (u.id = b.user_id)
51. 0.000 0.000 ↓ 0.0

Sort (cost=9,754.96..9,953.25 rows=396,587 width=4) (actual rows= loops=)

  • Sort Key: u.id
52. 0.000 0.000 ↓ 0.0

CTE Scan on u (cost=0.00..2,379.52 rows=396,587 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Materialize (cost=178,598.23..180,882.19 rows=2,283,953 width=64) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Sort (cost=178,598.23..179,740.21 rows=2,283,953 width=64) (actual rows= loops=)

  • Sort Key: b.user_id
55. 0.000 0.000 ↓ 0.0

Hash Join (cost=5.16..69,016.56 rows=2,283,953 width=64) (actual rows= loops=)

  • Hash Cond: (b.menu_id = m.menu_id)
56. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.85..49,943.72 rows=1,879,797 width=24) (actual rows= loops=)

  • Hash Cond: (b.menu_id = mi_1.menu_id)
57. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.03..42,892.66 rows=1,879,797 width=16) (actual rows= loops=)

  • Hash Cond: (b.basket_plan_id = bp.id)
58. 0.000 0.000 ↓ 0.0

Seq Scan on baskets b (cost=0.00..35,842.39 rows=1,879,797 width=16) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Hash (cost=1.01..1.01 rows=5 width=8) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Seq Scan on basket_plans bp (cost=0.00..1.01 rows=5 width=8) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash (cost=1.15..1.15 rows=191 width=12) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

CTE Scan on mi mi_1 (cost=0.00..1.15 rows=191 width=12) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=1.46..1.46 rows=243 width=44) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

CTE Scan on menu m (cost=0.00..1.46 rows=243 width=44) (actual rows= loops=)

65.          

CTE mstr_2

66. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=832,866.83..902,047.38 rows=12,567,372 width=116) (actual rows= loops=)

  • Merge Cond: ((non_eligible_grp.user_id = mstr.user_id) AND (non_eligible_grp.menu_id = mstr.menu_id))
67. 0.000 0.000 ↓ 0.0

Sort (cost=851.51..871.51 rows=40,000 width=24) (actual rows= loops=)

  • Sort Key: non_eligible_grp.user_id, non_eligible_grp.menu_id
68. 0.000 0.000 ↓ 0.0

CTE Scan on non_eligible_grp (cost=0.00..240.00 rows=40,000 width=24) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Materialize (cost=832,015.32..844,582.70 rows=12,567,372 width=92) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Sort (cost=832,015.32..838,299.01 rows=12,567,372 width=92) (actual rows= loops=)

  • Sort Key: mstr.user_id, mstr.menu_id
71. 0.000 0.000 ↓ 0.0

CTE Scan on mstr (cost=0.00..75,404.23 rows=12,567,372 width=92) (actual rows= loops=)

72.          

CTE mstr_3

73. 0.000 0.000 ↓ 0.0

CTE Scan on mstr_2 (cost=0.00..106,822.66 rows=12,567,372 width=140) (actual rows= loops=)