explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3lGZ : billing_period

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

Sort (cost=6,525,242.71..6,525,244.56 rows=741 width=385) (never executed)

  • Sort Key: tab.id
2. 0.000 0.000 ↓ 0.0

Subquery Scan on tab (cost=0..6,525,207.39 rows=741 width=385) (never executed)

3. 0.000 0.000 ↓ 0.0

Seq Scan on account_billing_period abp (cost=0..6,525,138.84 rows=741 width=241) (never executed)

4.          

SubPlan (for Seq Scan)

5. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,233.25..1,233.26 rows=1 width=32) (never executed)

6. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,233.21..1,233.23 rows=1 width=28) (never executed)

  • Group Key: a.id, e.id, sd.tz_date
7. 0.000 0.000 ↓ 0.0

Sort (cost=1,233.21..1,233.21 rows=1 width=28) (never executed)

  • Sort Key: e.id, sd.tz_date
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,233.2 rows=1 width=28) (never executed)

  • Filter: ((pc.provider_id IS NULL) OR (pc.provider_id <> 4))
9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,223.45 rows=1 width=36) (never executed)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,219.8 rows=1 width=36) (never executed)

11. 0.000 0.000 ↓ 0.0

Seq Scan on account_entity ae (cost=0..2.76 rows=1 width=16) (never executed)

  • Filter: (account_id = abp.account_id)
12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,216.39 rows=52 width=36) (never executed)

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,193.6 rows=52 width=20) (never executed)

14. 0.000 0.000 ↓ 0.0

Seq Scan on seat_day sd (cost=0..651.88 rows=81 width=12) (never executed)

  • Filter: ((tz_date >= (abp.billing_period_start_ts)::date) AND (tz_date <= ((abp.billing_period_end_ts - '1 day'::interval))::date))
15. 0.000 0.000 ↓ 0.0

Index Scan using pk__seat on seat s (cost=0.29..6.68 rows=1 width=16) (never executed)

  • Index Cond: (id = sd.seat_id)
  • Filter: ((seat_role_pl)::text <> 'EVENT_SERVER'::text)
16. 0.000 0.000 ↓ 0.0

Index Scan using pk__event on event e (cost=0.29..0.43 rows=1 width=24) (never executed)

  • Index Cond: (id = s.event_id)
17. 0.000 0.000 ↓ 0.0

Seq Scan on account a (cost=0..3.64 rows=1 width=8) (never executed)

  • Filter: (id = abp.account_id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on provider_config pc (cost=0..8.1 rows=110 width=16) (never executed)

19. 0.000 0.000 ↓ 0.0

Aggregate (cost=4,353.05..4,353.06 rows=1 width=32) (never executed)

20. 0.000 0.000 ↓ 0.0

Subquery Scan on acsdu (cost=735.89..4,351.43 rows=645 width=8) (never executed)

  • Filter: ((acsdu.tz_date >= (abp.billing_period_start_ts)::date) AND (acsdu.tz_date <= ((abp.billing_period_end_ts - '1 day'::interval))::date))
21. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=735.89..1,448.93 rows=129,000 width=28) (never executed)

  • Group Key: a_1.id, e_1.id, ((generate_series((timestamp_with_timezone_to_local_date_in_timezone(s_1.access_start, (e_1.timezone)::text))::timestamp with time zone, (timestamp_with_timezone_to_local_date_in_timezone(s_1.access_end, (e_1.timezone)::text))::timestamp with time zone, '1 day'::interval))::date)
22. 0.000 0.000 ↓ 0.0

Sort (cost=735.89..736.21 rows=129 width=28) (never executed)

  • Sort Key: e_1.id, ((generate_series((timestamp_with_timezone_to_local_date_in_timezone(s_1.access_start, (e_1.timezone)::text))::timestamp with time zone, (timestamp_with_timezone_to_local_date_in_timezone(s_1.access_end, (e_1.timezone)::text))::timestamp with time zone, '1 day'::interval))::date)
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12.54..731.36 rows=129 width=28) (never executed)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=12.25..590.4 rows=88 width=30) (never executed)

25. 0.000 0.000 ↓ 0.0

Seq Scan on account a_1 (cost=0..3.64 rows=1 width=8) (never executed)

  • Filter: (id = abp.account_id)
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.25..585.88 rows=88 width=30) (never executed)

  • Filter: ((pc_1.provider_id IS NULL) OR (pc_1.provider_id <> 4))
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.78..574.16 rows=138 width=38) (never executed)

28. 0.000 0.000 ↓ 0.0

Seq Scan on event e_1 (cost=0..538.37 rows=8,437 width=38) (never executed)

29. 0.000 0.000 ↓ 0.0

Hash (cost=2.76..2.76 rows=1 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0

Seq Scan on account_entity ae_1 (cost=0..2.76 rows=1 width=16) (never executed)

  • Filter: (account_id = abp.account_id)
31. 0.000 0.000 ↓ 0.0

Hash (cost=8.1..8.1 rows=110 width=16) (never executed)

32. 0.000 0.000 ↓ 0.0

Seq Scan on provider_config pc_1 (cost=0..8.1 rows=110 width=16) (never executed)

33. 0.000 0.000 ↓ 0.0

Index Scan using uk__seat__event_id__sequence on seat s_1 (cost=0.29..0.84 rows=1 width=32) (never executed)

  • Index Cond: (event_id = e_1.id)
  • Filter: (((seat_role_pl)::text <> 'EVENT_SERVER'::text) AND ((status_pl)::text <> 'CANCELED'::text))
34. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.18..1.19 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0

Seq Scan on account_subscription_level_change aslc1 (cost=0..1.18 rows=1 width=4) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'UPGRADE'::text))
36. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.18..1.19 rows=1 width=8) (never executed)

37. 0.000 0.000 ↓ 0.0

Seq Scan on account_subscription_level_change aslc2 (cost=0..1.18 rows=1 width=4) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'DOWNGRADE'::text))
38. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,237.88..1,237.9 rows=1 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,237.82..1,237.87 rows=1 width=28) (never executed)

  • Group Key: a_2.id, e_2.id, sd_1.tz_date
40. 0.000 0.000 ↓ 0.0

Sort (cost=1,237.82..1,237.82 rows=1 width=48) (never executed)

  • Sort Key: e_2.id, sd_1.tz_date
41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,237.81 rows=1 width=48) (never executed)

  • Filter: ((pc_2.provider_id IS NULL) OR (pc_2.provider_id <> 4))
42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,228.06 rows=1 width=56) (never executed)

43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,224.41 rows=1 width=56) (never executed)

44. 0.000 0.000 ↓ 0.0

Seq Scan on account_entity ae_2 (cost=0..2.76 rows=1 width=16) (never executed)

  • Filter: (account_id = abp.account_id)
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,220.82 rows=66 width=56) (never executed)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,193.6 rows=66 width=40) (never executed)

47. 0.000 0.000 ↓ 0.0

Seq Scan on seat_day sd_1 (cost=0..651.88 rows=81 width=40) (never executed)

  • Filter: ((tz_date >= (abp.billing_period_start_ts)::date) AND (tz_date <= ((abp.billing_period_end_ts - '1 day'::interval))::date))
48. 0.000 0.000 ↓ 0.0

Index Scan using pk__seat on seat s_2 (cost=0.29..6.68 rows=1 width=16) (never executed)

  • Index Cond: (id = sd_1.seat_id)
  • Filter: ((status_pl)::text <> 'CANCELED'::text)
49. 0.000 0.000 ↓ 0.0

Index Scan using pk__event on event e_2 (cost=0.29..0.4 rows=1 width=24) (never executed)

  • Index Cond: (id = s_2.event_id)
50. 0.000 0.000 ↓ 0.0

Seq Scan on account a_2 (cost=0..3.64 rows=1 width=8) (never executed)

  • Filter: (id = abp.account_id)
51. 0.000 0.000 ↓ 0.0

Seq Scan on provider_config pc_2 (cost=0..8.1 rows=110 width=16) (never executed)

52. 0.000 0.000 ↓ 0.0

Aggregate (cost=1,233.27..1,233.29 rows=1 width=8) (never executed)

53. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,233.21..1,233.26 rows=1 width=28) (never executed)

  • Group Key: a_3.id, e_3.id, sd_2.tz_date
54. 0.000 0.000 ↓ 0.0

Sort (cost=1,233.21..1,233.21 rows=1 width=48) (never executed)

  • Sort Key: e_3.id, sd_2.tz_date
55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,233.2 rows=1 width=48) (never executed)

  • Filter: ((pc_3.provider_id IS NULL) OR (pc_3.provider_id <> 4))
56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,223.45 rows=1 width=56) (never executed)

57. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,219.8 rows=1 width=56) (never executed)

58. 0.000 0.000 ↓ 0.0

Seq Scan on account_entity ae_3 (cost=0..2.76 rows=1 width=16) (never executed)

  • Filter: (account_id = abp.account_id)
59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..1,216.39 rows=52 width=56) (never executed)

60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,193.6 rows=52 width=40) (never executed)

61. 0.000 0.000 ↓ 0.0

Seq Scan on seat_day sd_2 (cost=0..651.88 rows=81 width=40) (never executed)

  • Filter: ((tz_date >= (abp.billing_period_start_ts)::date) AND (tz_date <= ((abp.billing_period_end_ts - '1 day'::interval))::date))
62. 0.000 0.000 ↓ 0.0

Index Scan using pk__seat on seat s_3 (cost=0.29..6.68 rows=1 width=16) (never executed)

  • Index Cond: (id = sd_2.seat_id)
  • Filter: ((seat_role_pl)::text <> 'EVENT_SERVER'::text)
63. 0.000 0.000 ↓ 0.0

Index Scan using pk__event on event e_3 (cost=0.29..0.43 rows=1 width=24) (never executed)

  • Index Cond: (id = s_3.event_id)
64. 0.000 0.000 ↓ 0.0

Seq Scan on account a_3 (cost=0..3.64 rows=1 width=8) (never executed)

  • Filter: (id = abp.account_id)
65. 0.000 0.000 ↓ 0.0

Seq Scan on provider_config pc_3 (cost=0..8.1 rows=110 width=16) (never executed)

66. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.03..1.04 rows=1 width=8) (never executed)

67. 0.000 0.000 ↓ 0.0

Seq Scan on account_vm_discount_plan_change avdpc1 (cost=0..1.03 rows=1 width=4) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'UPGRADE'::text))
68. 0.000 0.000 ↓ 0.0

Aggregate (cost=1.03..1.04 rows=1 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0

Seq Scan on account_vm_discount_plan_change avdpc2 (cost=0..1.03 rows=1 width=4) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'DOWNGRADE'::text))
70. 0.000 0.000 ↓ 0.0

Aggregate (cost=736.75..736.76 rows=1 width=32) (never executed)

71. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=736.71..736.74 rows=1 width=20) (never executed)

  • Group Key: a_4.id, ((timezone((e_4.timezone)::text, e_4.access_start))::date)
72. 0.000 0.000 ↓ 0.0

Sort (cost=736.71..736.72 rows=1 width=20) (never executed)

  • Sort Key: ((timezone((e_4.timezone)::text, e_4.access_start))::date)
73. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.14..736.7 rows=1 width=20) (never executed)

  • Filter: ((pc_4.provider_id IS NULL) OR (pc_4.provider_id <> 4))
74. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0..734.89 rows=1 width=46) (never executed)

75. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0..6.41 rows=1 width=16) (never executed)

76. 0.000 0.000 ↓ 0.0

Seq Scan on account a_4 (cost=0..3.64 rows=1 width=8) (never executed)

  • Filter: (id = abp.account_id)
77. 0.000 0.000 ↓ 0.0

Seq Scan on account_entity ae_4 (cost=0..2.76 rows=1 width=16) (never executed)

  • Filter: (account_id = abp.account_id)
78. 0.000 0.000 ↓ 0.0

Seq Scan on event e_4 (cost=0..728.2 rows=22 width=46) (never executed)

  • Filter: (is_use_self_paced_portal AND (NOT is_cancelled) AND ((timezone((timezone)::text, access_start))::date >= (abp.billing_period_start_ts)::date) AND ((timezone((timezone)::text, access_start))::date <= ((abp.billing_period_end_ts - '1 day'::interval))::date))
79. 0.000 0.000 ↓ 0.0

Index Scan using pk__provider_config on provider_config pc_4 (cost=0.14..1.8 rows=1 width=16) (never executed)

  • Index Cond: (e_4.provider_config_id = id)
80. 0.000 0.000 ↓ 0.0

Aggregate (cost=3.53..3.54 rows=1 width=8) (never executed)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.07..3.53 rows=1 width=4) (never executed)

82. 0.000 0.000 ↓ 0.0

Seq Scan on account_addon_plan_change aapc1 (cost=0..1.25 rows=1 width=12) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'UPGRADE'::text))
83. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.07..2.25 rows=2 width=8) (never executed)

84. 0.000 0.000 ↓ 0.0

Seq Scan on addon_plan ap1 (cost=0..1.12 rows=12 width=16) (never executed)

85. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=1 width=8) (never executed)

86. 0.000 0.000 ↓ 0.0

Seq Scan on addon a1 (cost=0..1.06 rows=1 width=8) (never executed)

  • Filter: ((type)::text = 'SELF_PACED_PORTAL'::text)
87. 0.000 0.000 ↓ 0.0

Aggregate (cost=3.53..3.54 rows=1 width=8) (never executed)

88. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.07..3.53 rows=1 width=4) (never executed)

89. 0.000 0.000 ↓ 0.0

Seq Scan on account_addon_plan_change aapc2 (cost=0..1.25 rows=1 width=12) (never executed)

  • Filter: ((account_billing_period_id = abp.id) AND ((type)::text = 'DOWNGRADE'::text))
90. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.07..2.25 rows=2 width=8) (never executed)

91. 0.000 0.000 ↓ 0.0

Seq Scan on addon_plan ap2 (cost=0..1.12 rows=12 width=16) (never executed)

92. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=1 width=8) (never executed)

93. 0.000 0.000 ↓ 0.0

Seq Scan on addon a2 (cost=0..1.06 rows=1 width=8) (never executed)

  • Filter: ((type)::text = 'SELF_PACED_PORTAL'::text)