explain.depesz.com

PostgreSQL's explain analyze made readable

Result: e7X

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

HashAggregate (cost=5,957,485.40..5,957,486.48 rows=108 width=64) (actual rows= loops=)

  • Group Key: b.id, b.theater_id, b.booking_date, r.id, r.title_id, r.release_date, r.country_id, hb.time_approved
2. 0.000 0.000 ↓ 0.0

Append (cost=2,666,772.42..5,957,483.24 rows=108 width=64) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=2,666,772.42..3,406,575.17 rows=36 width=51) (actual rows= loops=)

  • Hash Cond: (b.id = qr.booking_id)
4. 0.000 0.000 ↓ 0.0

Merge Join (cost=2,664,441.18..3,404,243.48 rows=36 width=51) (actual rows= loops=)

  • Merge Cond: ((b.theater_id = th.id) AND ((wd.country_id)::text = (th.country_id)::text))
  • Join Filter: ((SubPlan 5) OR (SubPlan 6))
5. 0.000 0.000 ↓ 0.0

Sort (cost=2,177,663.13..2,178,001.42 rows=135,316 width=86) (actual rows= loops=)

  • Sort Key: b.theater_id, wd.country_id
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=227,590.72..2,166,130.17 rows=135,316 width=86) (actual rows= loops=)

  • Hash Cond: ((b.theater_id = hb.theater_id) AND (r.title_id = hb.title_id))
  • Join Filter: (hb.week_start = snap_to_week((CASE WHEN (b.booking_date < r.release_date) THEN r.release_date ELSE b.booking_date END)::timestamp without time zone, wd.day1))
  • Filter: CASE WHEN (COALESCE(dos.distributor_id, (r.distributor_id)::text) = 'SONY'::text) THEN CASE WHEN thp.is_manual_entry THEN true ELSE ((hb.id IS NULL) OR (hb.time_approved IS NOT NULL)) END ELSE true END
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=30,432.82..1,867,145.53 rows=270,633 width=98) (actual rows= loops=)

  • Join Filter: ((b.booking_date >= wd.from_date) AND (b.booking_date <= wd.to_date))
8. 0.000 0.000 ↓ 0.0

Index Scan using uq_ix_week_definitions_by_country_2 on week_definitions_by_country wd (cost=0.14..150.04 rows=150 width=15) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Materialize (cost=30,432.67..1,824,411.34 rows=16,238 width=91) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=30,432.67..1,824,330.15 rows=16,238 width=91) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Hash Join (cost=30,432.11..1,451,901.06 rows=71,886 width=82) (actual rows= loops=)

  • Hash Cond: (b.release_id = r.id)
  • Join Filter: (CASE WHEN (r.release_date IS NOT NULL) THEN (b.booking_date >= (r.release_date - ('30 DAYS'::cstring)::interval)) ELSE true END AND (b.booking_date >= (r.release_date - ('30 DAYS'::cstring)::interval)) AND ((NOT CASE WHEN (r.release_date = '2019-09-27'::date) THEN (b.booking_date >= '2019-09-26'::date) WHEN ((r.release_date > '2019-09-27'::date) AND (r.release_date <= '2019-10-03'::date)) THEN (b.booking_date >= '2019-09-27'::date) ELSE ((b.booking_date >= '2019-09-27'::date) AND (b.booking_date >= r.release_date)) END) OR (b.booking_date > '2019-10-03'::date)))
12. 0.000 0.000 ↓ 0.0

Seq Scan on bookings b (cost=0.00..1,282,841.91 rows=25,566,186 width=44) (actual rows= loops=)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
13. 0.000 0.000 ↓ 0.0

Hash (cost=30,135.82..30,135.82 rows=23,703 width=38) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=30,115.59..30,135.82 rows=23,703 width=38) (actual rows= loops=)

  • Hash Cond: ((dos.title_id = r.title_id) AND (dos.country_id = (r.country_id)::text))
  • Filter: (((COALESCE(dos.distributor_id, (r.distributor_id)::text) <> ALL ('{UNITED,MGM1,ANNAPURNA1,ORION1,OCL}'::text[])) OR (r.release_date >= '2018-03-01'::date)) AND (COALESCE(dos.distributor_id, (r.distributor_id)::text) = ANY ('{APPARITION1,COLUMBIA1,SCREEN6,SONY,SONY4,SONY7,SONY8,TRI}'::text[])))
15. 0.000 0.000 ↓ 0.0

Seq Scan on distributors_overrides dos (cost=0.00..1.10 rows=10 width=18) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash (cost=21,031.79..21,031.79 rows=605,587 width=31) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on releases r (cost=0.00..21,031.79 rows=605,587 width=31) (actual rows= loops=)

  • Filter: (release_date <= '2019-10-03'::date)
18. 0.000 0.000 ↓ 0.0

Index Scan using uq_theater_properties_1 on theater_properties thp (cost=0.56..5.18 rows=1 width=13) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
19. 0.000 0.000 ↓ 0.0

Hash (cost=142,038.56..142,038.56 rows=2,416,156 width=36) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on held_back_weekly_titles hb (cost=0.00..142,038.56 rows=2,416,156 width=36) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Sort (cost=486,617.51..491,973.34 rows=2,142,331 width=11) (actual rows= loops=)

  • Sort Key: th.id, th.country_id
22. 0.000 0.000 ↓ 0.0

Seq Scan on theaters th (cost=0.00..261,343.38 rows=2,142,331 width=11) (actual rows= loops=)

  • Filter: (id <> ALL ('{936121,936883,937400,940621,940665,940666,945089,945310,975437,975438,994803,1042894,1054028,1058439,1058485,1061071,1063880}'::bigint[]))
23.          

SubPlan (forMerge Join)

24. 0.000 0.000 ↓ 0.0

HashAggregate (cost=536.37..536.40 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
25. 0.000 0.000 ↓ 0.0

Append (cost=2.39..536.36 rows=3 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.39..170.30 rows=1 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.82..161.71 rows=1 width=0) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (((ft_6.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_6.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_15.time_approved IS NOT NULL) AND (hb_15.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_15.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
29. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (hb_15.week_start = snap_to_week((CASE WHEN (b.booking_date < r_15.release_date) THEN r_15.release_date ELSE b.booking_date END)::timestamp without time zone, wd_15.day1))
30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_15 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
32. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_15 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
33. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_15 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_15.title_id))
34. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_6 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
35. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_12 (cost=0.28..7.05 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_6.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
36. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_15 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.81..178.79 rows=1 width=4) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..170.19 rows=1 width=0) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_16.distributor_id)::text <> (primary_release_8.distributor_id)::text)
40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_7.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_7.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_16.time_approved IS NOT NULL) AND (hb_16.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_16.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_16.week_start = snap_to_week((CASE WHEN (b.booking_date < r_16.release_date) THEN r_16.release_date ELSE b.booking_date END)::timestamp without time zone, wd_16.day1))
42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_16 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b.secondary_release_id = id)
44. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_16 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
45. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_16 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_16.title_id))
46. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_7 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
47. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_8 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
48. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_13 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_7.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
49. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_16 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.24..187.24 rows=1 width=4) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..178.65 rows=1 width=0) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.40..171.56 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_17.distributor_id)::text <> (secondary_release_4.distributor_id)::text)
53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=16) (actual rows= loops=)

  • Join Filter: ((r_17.distributor_id)::text <> (primary_release_9.distributor_id)::text)
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_8.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_8.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_17.time_approved IS NOT NULL) AND (hb_17.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_17.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
55. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_17.week_start = snap_to_week((CASE WHEN (b.booking_date < r_17.release_date) THEN r_17.release_date ELSE b.booking_date END)::timestamp without time zone, wd_17.day1))
56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_17 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b.tertiary_release_id = id)
58. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_17 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
59. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_17 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_17.title_id))
60. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_8 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
61. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_9 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
62. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release_4 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.secondary_release_id = id)
63. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_14 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_8.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
64. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_17 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
65. 0.000 0.000 ↓ 0.0

HashAggregate (cost=170.25..170.28 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
66. 0.000 0.000 ↓ 0.0

Append (cost=2.25..170.25 rows=3 width=4) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..48.28 rows=1 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.68..39.69 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_18.week_start = snap_to_week((CASE WHEN (b.booking_date < r_18.release_date) THEN r_18.release_date ELSE b.booking_date END)::timestamp without time zone, wd_18.day1))
  • Filter: (((frj_6.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_6.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_18.time_approved IS NOT NULL) AND (hb_18.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_18.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_6 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
73. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_18 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
74. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_15 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_6.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
75. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_18 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
76. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_18 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_18.title_id))
77. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_18 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
78. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..56.74 rows=1 width=4) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.11..48.14 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_19.week_start = snap_to_week((CASE WHEN (b.booking_date < r_19.release_date) THEN r_19.release_date ELSE b.booking_date END)::timestamp without time zone, wd_19.day1))
  • Filter: (((frj_7.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_7.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_19.time_approved IS NOT NULL) AND (hb_19.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_19.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..39.29 rows=1 width=24) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..33.66 rows=1 width=20) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_19.distributor_id)::text <> (primary_release_10.distributor_id)::text)
83. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_7 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
85. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_19 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b.secondary_release_id = id)
86. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_10 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
87. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_16 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_7.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
88. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_19 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
89. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_19 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_19.title_id))
90. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_19 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
91. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.10..65.19 rows=1 width=4) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.53..56.60 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_20.week_start = snap_to_week((CASE WHEN (b.booking_date < r_20.release_date) THEN r_20.release_date ELSE b.booking_date END)::timestamp without time zone, wd_20.day1))
  • Filter: (((frj_8.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_8.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_20.time_approved IS NOT NULL) AND (hb_20.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_20.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
93. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_20.distributor_id)::text <> (secondary_release_5.distributor_id)::text)
96. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual rows= loops=)

  • Join Filter: ((r_20.distributor_id)::text <> (primary_release_11.distributor_id)::text)
97. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_8 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b.id = booking_id)
99. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_20 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b.tertiary_release_id = id)
100. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_11 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.release_id = id)
101. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release_5 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b.secondary_release_id = id)
102. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_17 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_8.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
103. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_20 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
104. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_20 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_20.title_id))
105. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_20 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
106. 0.000 0.000 ↓ 0.0

Hash (cost=2,273.46..2,273.46 rows=4,622 width=8) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on queued_resends qr (cost=160.24..2,273.46 rows=4,622 width=8) (actual rows= loops=)

  • Recheck Cond: (time_sent IS NULL)
108. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..159.08 rows=4,622 width=0) (actual rows= loops=)

  • Index Cond: (time_sent IS NULL)
109. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=1,503,428.92..2,243,231.67 rows=36 width=51) (actual rows= loops=)

  • Hash Cond: (b_1.id = qr_1.booking_id)
110. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,501,097.68..2,240,899.98 rows=36 width=51) (actual rows= loops=)

  • Merge Cond: ((b_1.theater_id = th_1.id) AND ((wd_1.country_id)::text = (th_1.country_id)::text))
  • Join Filter: ((SubPlan 3) OR (SubPlan 4))
111. 0.000 0.000 ↓ 0.0

Sort (cost=1,014,319.63..1,014,657.92 rows=135,316 width=86) (actual rows= loops=)

  • Sort Key: b_1.theater_id, wd_1.country_id
112. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=229,018.56..1,002,786.67 rows=135,316 width=86) (actual rows= loops=)

  • Hash Cond: ((b_1.theater_id = hb_1.theater_id) AND (r_1.title_id = hb_1.title_id))
  • Join Filter: (hb_1.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_1.release_date) THEN r_1.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_1.day1))
  • Filter: CASE WHEN (COALESCE(dos_1.distributor_id, (r_1.distributor_id)::text) = 'SONY'::text) THEN CASE WHEN thp_1.is_manual_entry THEN true ELSE ((hb_1.id IS NULL) OR (hb_1.time_approved IS NOT NULL)) END ELSE true END
113. 0.000 0.000 ↓ 0.0

Nested Loop (cost=31,860.66..703,802.03 rows=270,633 width=98) (actual rows= loops=)

  • Join Filter: ((b_1.booking_date >= wd_1.from_date) AND (b_1.booking_date <= wd_1.to_date))
114. 0.000 0.000 ↓ 0.0

Index Scan using uq_ix_week_definitions_by_country_2 on week_definitions_by_country wd_1 (cost=0.14..150.04 rows=150 width=15) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Materialize (cost=31,860.51..661,067.84 rows=16,238 width=91) (actual rows= loops=)

116. 0.000 0.000 ↓ 0.0

Merge Join (cost=31,860.51..660,986.65 rows=16,238 width=91) (actual rows= loops=)

  • Merge Cond: (b_1.secondary_release_id = r_1.id)
  • Join Filter: (CASE WHEN (r_1.release_date IS NOT NULL) THEN (b_1.booking_date >= (r_1.release_date - ('30 DAYS'::cstring)::interval)) ELSE true END AND (b_1.booking_date >= (r_1.release_date - ('30 DAYS'::cstring)::interval)) AND ((NOT CASE WHEN (r_1.release_date = '2019-09-27'::date) THEN (b_1.booking_date >= '2019-09-26'::date) WHEN ((r_1.release_date > '2019-09-27'::date) AND (r_1.release_date <= '2019-10-03'::date)) THEN (b_1.booking_date >= '2019-09-27'::date) ELSE ((b_1.booking_date >= '2019-09-27'::date) AND (b_1.booking_date >= r_1.release_date)) END) OR (b_1.booking_date > '2019-10-03'::date)))
117. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..235,860,262.58 rows=5,775,148 width=53) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Index Scan using ix_bookings_secondary_release_1 on bookings b_1 (cost=0.56..103,406,219.77 rows=25,566,186 width=44) (actual rows= loops=)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
119. 0.000 0.000 ↓ 0.0

Index Scan using uq_theater_properties_1 on theater_properties thp_1 (cost=0.56..5.18 rows=1 width=13) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
120. 0.000 0.000 ↓ 0.0

Sort (cost=31,858.17..31,917.43 rows=23,703 width=38) (actual rows= loops=)

  • Sort Key: r_1.id
121. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=30,115.59..30,135.82 rows=23,703 width=38) (actual rows= loops=)

  • Hash Cond: ((dos_1.title_id = r_1.title_id) AND (dos_1.country_id = (r_1.country_id)::text))
  • Filter: (((COALESCE(dos_1.distributor_id, (r_1.distributor_id)::text) <> ALL ('{UNITED,MGM1,ANNAPURNA1,ORION1,OCL}'::text[])) OR (r_1.release_date >= '2018-03-01'::date)) AND (COALESCE(dos_1.distributor_id, (r_1.distributor_id)::text) = ANY ('{APPARITION1,COLUMBIA1,SCREEN6,SONY,SONY4,SONY7,SONY8,TRI}'::text[])))
122. 0.000 0.000 ↓ 0.0

Seq Scan on distributors_overrides dos_1 (cost=0.00..1.10 rows=10 width=18) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

Hash (cost=21,031.79..21,031.79 rows=605,587 width=31) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

Seq Scan on releases r_1 (cost=0.00..21,031.79 rows=605,587 width=31) (actual rows= loops=)

  • Filter: (release_date <= '2019-10-03'::date)
125. 0.000 0.000 ↓ 0.0

Hash (cost=142,038.56..142,038.56 rows=2,416,156 width=36) (actual rows= loops=)

126. 0.000 0.000 ↓ 0.0

Seq Scan on held_back_weekly_titles hb_1 (cost=0.00..142,038.56 rows=2,416,156 width=36) (actual rows= loops=)

127. 0.000 0.000 ↓ 0.0

Sort (cost=486,617.51..491,973.34 rows=2,142,331 width=11) (actual rows= loops=)

  • Sort Key: th_1.id, th_1.country_id
128. 0.000 0.000 ↓ 0.0

Seq Scan on theaters th_1 (cost=0.00..261,343.38 rows=2,142,331 width=11) (actual rows= loops=)

  • Filter: (id <> ALL ('{936121,936883,937400,940621,940665,940666,945089,945310,975437,975438,994803,1042894,1054028,1058439,1058485,1061071,1063880}'::bigint[]))
129.          

SubPlan (forMerge Join)

130. 0.000 0.000 ↓ 0.0

HashAggregate (cost=536.37..536.40 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
131. 0.000 0.000 ↓ 0.0

Append (cost=2.39..536.36 rows=3 width=4) (actual rows= loops=)

132. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.39..170.30 rows=1 width=4) (actual rows= loops=)

133. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.82..161.71 rows=1 width=0) (actual rows= loops=)

134. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (((ft_3.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_3.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_9.time_approved IS NOT NULL) AND (hb_9.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_9.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
135. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (hb_9.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_9.release_date) THEN r_9.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_9.day1))
136. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_9 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
138. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_9 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
139. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_9 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_9.title_id))
140. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_3 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
141. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_6 (cost=0.28..7.05 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_3.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
142. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_9 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
143. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.81..178.79 rows=1 width=4) (actual rows= loops=)

144. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..170.19 rows=1 width=0) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_10.distributor_id)::text <> (primary_release_4.distributor_id)::text)
146. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_4.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_4.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_10.time_approved IS NOT NULL) AND (hb_10.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_10.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
147. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_10.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_10.release_date) THEN r_10.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_10.day1))
148. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_10 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_1.secondary_release_id = id)
150. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_10 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
151. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_10 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_10.title_id))
152. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_4 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
153. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_4 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
154. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_7 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_4.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
155. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_10 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
156. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.24..187.24 rows=1 width=4) (actual rows= loops=)

157. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..178.65 rows=1 width=0) (actual rows= loops=)

158. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.40..171.56 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_11.distributor_id)::text <> (secondary_release_2.distributor_id)::text)
159. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=16) (actual rows= loops=)

  • Join Filter: ((r_11.distributor_id)::text <> (primary_release_5.distributor_id)::text)
160. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_5.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_5.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_11.time_approved IS NOT NULL) AND (hb_11.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_11.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
161. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_11.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_11.release_date) THEN r_11.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_11.day1))
162. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

163. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_11 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_1.tertiary_release_id = id)
164. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_11 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
165. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_11 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_11.title_id))
166. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_5 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
167. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_5 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
168. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release_2 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.secondary_release_id = id)
169. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_8 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_5.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
170. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_11 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
171. 0.000 0.000 ↓ 0.0

HashAggregate (cost=170.25..170.28 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
172. 0.000 0.000 ↓ 0.0

Append (cost=2.25..170.25 rows=3 width=4) (actual rows= loops=)

173. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..48.28 rows=1 width=4) (actual rows= loops=)

174. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.68..39.69 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_12.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_12.release_date) THEN r_12.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_12.day1))
  • Filter: (((frj_3.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_3.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_12.time_approved IS NOT NULL) AND (hb_12.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_12.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
175. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual rows= loops=)

176. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual rows= loops=)

177. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual rows= loops=)

178. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_3 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
179. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_12 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
180. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_9 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_3.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
181. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_12 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
182. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_12 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_12.title_id))
183. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_12 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
184. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..56.74 rows=1 width=4) (actual rows= loops=)

185. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.11..48.14 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_13.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_13.release_date) THEN r_13.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_13.day1))
  • Filter: (((frj_4.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_4.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_13.time_approved IS NOT NULL) AND (hb_13.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_13.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
186. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..39.29 rows=1 width=24) (actual rows= loops=)

187. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..33.66 rows=1 width=20) (actual rows= loops=)

188. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_13.distributor_id)::text <> (primary_release_6.distributor_id)::text)
189. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

190. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_4 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
191. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_13 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_1.secondary_release_id = id)
192. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_6 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
193. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_10 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_4.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
194. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_13 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
195. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_13 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_13.title_id))
196. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_13 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
197. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.10..65.19 rows=1 width=4) (actual rows= loops=)

198. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.53..56.60 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_14.week_start = snap_to_week((CASE WHEN (b_1.booking_date < r_14.release_date) THEN r_14.release_date ELSE b_1.booking_date END)::timestamp without time zone, wd_14.day1))
  • Filter: (((frj_5.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_5.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_14.time_approved IS NOT NULL) AND (hb_14.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_14.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
199. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual rows= loops=)

200. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual rows= loops=)

201. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_14.distributor_id)::text <> (secondary_release_3.distributor_id)::text)
202. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual rows= loops=)

  • Join Filter: ((r_14.distributor_id)::text <> (primary_release_7.distributor_id)::text)
203. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

204. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_5 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_1.id = booking_id)
205. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_14 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_1.tertiary_release_id = id)
206. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_7 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.release_id = id)
207. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release_3 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_1.secondary_release_id = id)
208. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_11 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_5.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
209. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_14 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
210. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_14 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_14.title_id))
211. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_14 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
212. 0.000 0.000 ↓ 0.0

Hash (cost=2,273.46..2,273.46 rows=4,622 width=8) (actual rows= loops=)

213. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on queued_resends qr_1 (cost=160.24..2,273.46 rows=4,622 width=8) (actual rows= loops=)

  • Recheck Cond: (time_sent IS NULL)
214. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..159.08 rows=4,622 width=0) (actual rows= loops=)

  • Index Cond: (time_sent IS NULL)
215. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=32,085.33..307,675.31 rows=36 width=51) (actual rows= loops=)

  • Join Filter: (hb_2.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_2.release_date) THEN r_2.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_2.day1))
  • Filter: CASE WHEN (COALESCE(dos_2.distributor_id, (r_2.distributor_id)::text) = 'SONY'::text) THEN CASE WHEN thp_2.is_manual_entry THEN true ELSE ((hb_2.id IS NULL) OR (hb_2.time_approved IS NOT NULL)) END ELSE true END
216. 0.000 0.000 ↓ 0.0

Hash Join (cost=32,084.77..307,306.47 rows=71 width=63) (actual rows= loops=)

  • Hash Cond: ((th_2.country_id)::text = (wd_2.country_id)::text)
  • Join Filter: ((b_2.booking_date >= wd_2.from_date) AND (b_2.booking_date <= wd_2.to_date))
217. 0.000 0.000 ↓ 0.0

Merge Join (cost=32,078.40..307,287.66 rows=565 width=62) (actual rows= loops=)

  • Merge Cond: (b_2.tertiary_release_id = r_2.id)
  • Join Filter: (CASE WHEN (r_2.release_date IS NOT NULL) THEN (b_2.booking_date >= (r_2.release_date - ('30 DAYS'::cstring)::interval)) ELSE true END AND (b_2.booking_date >= (r_2.release_date - ('30 DAYS'::cstring)::interval)) AND ((NOT CASE WHEN (r_2.release_date = '2019-09-27'::date) THEN (b_2.booking_date >= '2019-09-26'::date) WHEN ((r_2.release_date > '2019-09-27'::date) AND (r_2.release_date <= '2019-10-03'::date)) THEN (b_2.booking_date >= '2019-09-27'::date) ELSE ((b_2.booking_date >= '2019-09-27'::date) AND (b_2.booking_date >= r_2.release_date)) END) OR (b_2.booking_date > '2019-10-03'::date)))
218. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=161.80..4,255,455,942.79 rows=201,058 width=32) (actual rows= loops=)

  • Join Filter: (qr_2.booking_id = b_2.id)
219. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..4,241,513,058.70 rows=201,076 width=32) (actual rows= loops=)

220. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..134,648,799.11 rows=5,775,148 width=53) (actual rows= loops=)

221. 0.000 0.000 ↓ 0.0

Index Scan using ix_bookings_tertiary_release_1 on bookings b_2 (cost=0.56..2,194,756.30 rows=25,566,186 width=44) (actual rows= loops=)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
222. 0.000 0.000 ↓ 0.0

Index Scan using uq_theater_properties_1 on theater_properties thp_2 (cost=0.56..5.18 rows=1 width=13) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
223. 0.000 0.000 ↓ 0.0

Index Scan using pk_theaters on theaters th_2 (cost=0.43..711.12 rows=1 width=11) (actual rows= loops=)

  • Index Cond: (id = b_2.theater_id)
  • Filter: ((id <> ALL ('{936121,936883,937400,940621,940665,940666,945089,945310,975437,975438,994803,1042894,1054028,1058439,1058485,1061071,1063880}'::bigint[])) AND ((SubPlan 1) OR (SubPlan 2)))
224.          

SubPlan (forIndex Scan)

225. 0.000 0.000 ↓ 0.0

HashAggregate (cost=536.37..536.40 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
226. 0.000 0.000 ↓ 0.0

Append (cost=2.39..536.36 rows=3 width=4) (actual rows= loops=)

227. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.39..170.30 rows=1 width=4) (actual rows= loops=)

228. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.82..161.71 rows=1 width=0) (actual rows= loops=)

229. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (((ft.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_3.time_approved IS NOT NULL) AND (hb_3.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_3.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
230. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual rows= loops=)

  • Join Filter: (hb_3.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_3.release_date) THEN r_3.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_3.day1))
231. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual rows= loops=)

232. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_3 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
233. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_3 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
234. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_3 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_3.title_id))
235. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
236. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u (cost=0.28..7.05 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
237. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_3 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
238. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.81..178.79 rows=1 width=4) (actual rows= loops=)

239. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..170.19 rows=1 width=0) (actual rows= loops=)

240. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_4.distributor_id)::text <> (primary_release.distributor_id)::text)
241. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_1.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_1.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_4.time_approved IS NOT NULL) AND (hb_4.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_4.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
242. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_4.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_4.release_date) THEN r_4.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_4.day1))
243. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

244. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_4 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_2.secondary_release_id = id)
245. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_4 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
246. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_4 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_4.title_id))
247. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_1 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
248. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
249. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_1 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_1.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
250. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_4 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
251. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.24..187.24 rows=1 width=4) (actual rows= loops=)

252. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..178.65 rows=1 width=0) (actual rows= loops=)

253. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.40..171.56 rows=1 width=8) (actual rows= loops=)

  • Join Filter: ((r_5.distributor_id)::text <> (secondary_release.distributor_id)::text)
254. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.97..163.10 rows=1 width=16) (actual rows= loops=)

  • Join Filter: ((r_5.distributor_id)::text <> (primary_release_1.distributor_id)::text)
255. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.54..154.65 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (((ft_2.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (ft_2.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_5.time_approved IS NOT NULL) AND (hb_5.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_5.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
256. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual rows= loops=)

  • Join Filter: (hb_5.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_5.release_date) THEN r_5.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_5.day1))
257. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual rows= loops=)

258. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_5 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_2.tertiary_release_id = id)
259. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_5 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
260. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_5 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_5.title_id))
261. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_2 (cost=0.56..129.23 rows=125 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
262. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_1 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
263. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.secondary_release_id = id)
264. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_2 (cost=0.28..7.09 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = ft_2.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
265. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_5 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
266. 0.000 0.000 ↓ 0.0

HashAggregate (cost=170.25..170.28 rows=3 width=4) (actual rows= loops=)

  • Group Key: (1)
267. 0.000 0.000 ↓ 0.0

Append (cost=2.25..170.25 rows=3 width=4) (actual rows= loops=)

268. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.25..48.28 rows=1 width=4) (actual rows= loops=)

269. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.68..39.69 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_6.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_6.release_date) THEN r_6.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_6.day1))
  • Filter: (((frj.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_6.time_approved IS NOT NULL) AND (hb_6.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_6.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
270. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual rows= loops=)

271. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual rows= loops=)

272. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual rows= loops=)

273. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
274. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_6 (cost=0.42..8.44 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
275. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_3 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
276. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_6 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
277. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_6 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_6.title_id))
278. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_6 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
279. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.67..56.74 rows=1 width=4) (actual rows= loops=)

280. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.11..48.14 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_7.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_7.release_date) THEN r_7.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_7.day1))
  • Filter: (((frj_1.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_1.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_7.time_approved IS NOT NULL) AND (hb_7.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_7.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
281. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..39.29 rows=1 width=24) (actual rows= loops=)

282. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..33.66 rows=1 width=20) (actual rows= loops=)

283. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_7.distributor_id)::text <> (primary_release_2.distributor_id)::text)
284. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

285. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_1 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
286. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_7 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_2.secondary_release_id = id)
287. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_2 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
288. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_4 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_1.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
289. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_7 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
290. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_7 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_7.title_id))
291. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_7 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
292. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.10..65.19 rows=1 width=4) (actual rows= loops=)

293. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.53..56.60 rows=1 width=0) (actual rows= loops=)

  • Join Filter: (hb_8.week_start = snap_to_week((CASE WHEN (b_2.booking_date < r_8.release_date) THEN r_8.release_date ELSE b_2.booking_date END)::timestamp without time zone, wd_8.day1))
  • Filter: (((frj_2.time_finalized > '2019-10-04 00:00:00'::timestamp without time zone) AND (frj_2.time_finalized <= '2019-10-04 13:00:00'::timestamp without time zone)) OR ((hb_8.time_approved IS NOT NULL) AND (hb_8.time_approved > '2019-10-04 00:00:00'::timestamp without time zone) AND (hb_8.time_approved <= '2019-10-04 13:00:00'::timestamp without time zone)))
294. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual rows= loops=)

295. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual rows= loops=)

296. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual rows= loops=)

  • Join Filter: ((r_8.distributor_id)::text <> (secondary_release_1.distributor_id)::text)
297. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual rows= loops=)

  • Join Filter: ((r_8.distributor_id)::text <> (primary_release_3.distributor_id)::text)
298. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual rows= loops=)

299. 0.000 0.000 ↓ 0.0

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_2 (cost=0.42..8.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (b_2.id = booking_id)
300. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases r_8 (cost=0.42..8.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (b_2.tertiary_release_id = id)
301. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases primary_release_3 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.release_id = id)
302. 0.000 0.000 ↓ 0.0

Index Scan using pk_releases on releases secondary_release_1 (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (b_2.secondary_release_id = id)
303. 0.000 0.000 ↓ 0.0

Index Scan using pk_users on users u_5 (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = frj_2.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
304. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_8 (cost=0.00..5.62 rows=1 width=4) (actual rows= loops=)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
305. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_8 (cost=0.56..8.57 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_8.title_id))
306. 0.000 0.000 ↓ 0.0

Index Only Scan using uq_theater_properties_1 on theater_properties thp_8 (cost=0.56..8.58 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
307. 0.000 0.000 ↓ 0.0

Materialize (cost=160.24..2,296.57 rows=4,622 width=8) (actual rows= loops=)

308. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on queued_resends qr_2 (cost=160.24..2,273.46 rows=4,622 width=8) (actual rows= loops=)

  • Recheck Cond: (time_sent IS NULL)
309. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..159.08 rows=4,622 width=0) (actual rows= loops=)

  • Index Cond: (time_sent IS NULL)
310. 0.000 0.000 ↓ 0.0

Sort (cost=31,858.17..31,917.43 rows=23,703 width=38) (actual rows= loops=)

  • Sort Key: r_2.id
311. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=30,115.59..30,135.82 rows=23,703 width=38) (actual rows= loops=)

  • Hash Cond: ((dos_2.title_id = r_2.title_id) AND (dos_2.country_id = (r_2.country_id)::text))
  • Filter: (((COALESCE(dos_2.distributor_id, (r_2.distributor_id)::text) <> ALL ('{UNITED,MGM1,ANNAPURNA1,ORION1,OCL}'::text[])) OR (r_2.release_date >= '2018-03-01'::date)) AND (COALESCE(dos_2.distributor_id, (r_2.distributor_id)::text) = ANY ('{APPARITION1,COLUMBIA1,SCREEN6,SONY,SONY4,SONY7,SONY8,TRI}'::text[])))
312. 0.000 0.000 ↓ 0.0

Seq Scan on distributors_overrides dos_2 (cost=0.00..1.10 rows=10 width=18) (actual rows= loops=)

313. 0.000 0.000 ↓ 0.0

Hash (cost=21,031.79..21,031.79 rows=605,587 width=31) (actual rows= loops=)

314. 0.000 0.000 ↓ 0.0

Seq Scan on releases r_2 (cost=0.00..21,031.79 rows=605,587 width=31) (actual rows= loops=)

  • Filter: (release_date <= '2019-10-03'::date)
315. 0.000 0.000 ↓ 0.0

Hash (cost=4.50..4.50 rows=150 width=15) (actual rows= loops=)

316. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country wd_2 (cost=0.00..4.50 rows=150 width=15) (actual rows= loops=)

317. 0.000 0.000 ↓ 0.0

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_2 (cost=0.56..4.92 rows=1 width=36) (actual rows= loops=)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_2.title_id))