explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wm2q

Settings
# exclusive inclusive rows x rows loops node
1. 1.243 10,352,248.617 ↓ 2.4 202 1

HashAggregate (cost=6,665,679.41..6,665,680.25 rows=84 width=64) (actual time=10,352,248.158..10,352,248.617 rows=202 loops=1)

  • 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.941 10,352,247.374 ↓ 2.4 202 1

Append (cost=2,868,361.59..6,665,677.73 rows=84 width=64) (actual time=7,575,098.237..10,352,247.374 rows=202 loops=1)

3. 1.178 10,225,787.260 ↓ 7.1 199 1

Hash Anti Join (cost=2,868,361.59..3,610,202.00 rows=28 width=51) (actual time=7,575,098.232..10,225,787.260 rows=199 loops=1)

  • Hash Cond: (b.id = qr.booking_id)
4. 846,884.259 10,225,702.201 ↓ 7.1 199 1

Merge Join (cost=2,866,036.98..3,607,877.04 rows=28 width=51) (actual time=7,575,014.315..10,225,702.201 rows=199 loops=1)

  • Merge Cond: ((th.id = b.theater_id) AND ((th.country_id)::text = (wd.country_id)::text))
  • Join Filter: ((SubPlan 5) OR (SubPlan 6))
  • Rows Removed by Join Filter: 2660364
5. 570.235 1,718.356 ↑ 22.8 118,143 1

Sort (cost=611,710.12..618,435.10 rows=2,689,991 width=11) (actual time=1,424.382..1,718.356 rows=118,143 loops=1)

  • Sort Key: th.id, th.country_id
  • Sort Method: quicksort Memory: 8652kB
6. 1,148.121 1,148.121 ↑ 22.6 119,022 1

Seq Scan on theaters th (cost=0.00..278,457.75 rows=2,689,991 width=11) (actual time=3.635..1,148.121 rows=119,022 loops=1)

  • Filter: (id <> ALL ('{936121,936883,937400,940621,940665,940666,945089,945310,975437,975438,994803,1042894,1054028,1058439,1058485,1061071,1063880}'::bigint[]))
  • Rows Removed by Filter: 17
7. 2,463,935.173 8,355,463.869 ↓ 2,763.4 377,140,990 1

Sort (cost=2,254,126.50..2,254,467.69 rows=136,475 width=86) (actual time=7,517,418.370..8,355,463.869 rows=377,140,990 loops=1)

  • Sort Key: b.theater_id, wd.country_id
  • Sort Method: external sort Disk: 33293464kB
8. 3,001,397.988 5,891,528.696 ↓ 2,763.4 377,140,990 1

Hash Left Join (cost=284,058.41..2,242,486.36 rows=136,475 width=86) (actual time=9,074.641..5,891,528.696 rows=377,140,990 loops=1)

  • 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))
  • Rows Removed by Join Filter: 425578160
  • 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
  • Rows Removed by Filter: 3817490
9. 1,698,826.220 2,885,420.071 ↓ 1,395.7 380,958,480 1

Nested Loop (cost=30,473.41..1,872,929.87 rows=272,950 width=98) (actual time=4,028.303..2,885,420.071 rows=380,958,480 loops=1)

  • Join Filter: ((b.booking_date >= wd.from_date) AND (b.booking_date <= wd.to_date))
  • Rows Removed by Join Filter: 27211320
10. 2.501 2.501 ↑ 1.0 150 1

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 time=0.893..2.501 rows=150 loops=1)

11. 882,019.403 1,186,591.350 ↓ 166.2 2,721,132 150

Materialize (cost=30,473.27..1,829,831.15 rows=16,377 width=91) (actual time=26.863..7,910.609 rows=2,721,132 loops=150)

12. 24,496.948 304,571.947 ↓ 166.2 2,721,132 1

Nested Loop (cost=30,473.27..1,829,749.26 rows=16,377 width=91) (actual time=4,027.391..304,571.947 rows=2,721,132 loops=1)

13. 121,099.029 239,659.374 ↓ 62.1 4,490,625 1

Hash Join (cost=30,472.70..1,455,069.06 rows=72,285 width=82) (actual time=3,081.260..239,659.374 rows=4,490,625 loops=1)

  • 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)))
  • Rows Removed by Join Filter: 5161
14. 115,479.435 115,479.435 ↓ 2.0 51,591,343 1

Seq Scan on bookings b (cost=0.00..1,285,531.33 rows=25,620,101 width=44) (actual time=0.032..115,479.435 rows=51,591,343 loops=1)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
  • Rows Removed by Filter: 153468
15. 13.346 3,080.910 ↑ 4.4 5,407 1

Hash (cost=30,175.31..30,175.31 rows=23,791 width=38) (actual time=3,080.910..3,080.910 rows=5,407 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 581kB
16. 255.048 3,067.564 ↑ 4.4 5,407 1

Hash Right Join (cost=30,155.69..30,175.31 rows=23,791 width=38) (actual time=2,817.908..3,067.564 rows=5,407 loops=1)

  • 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[])))
  • Rows Removed by Filter: 601258
17. 0.027 0.027 ↑ 1.0 10 1

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

18. 1,414.465 2,812.489 ↑ 1.0 606,665 1

Hash (cost=21,037.55..21,037.55 rows=607,876 width=31) (actual time=2,812.489..2,812.489 rows=606,665 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45454kB
19. 1,398.024 1,398.024 ↑ 1.0 606,665 1

Seq Scan on releases r (cost=0.00..21,037.55 rows=607,876 width=31) (actual time=0.016..1,398.024 rows=606,665 loops=1)

  • Filter: (release_date <= '2019-10-03'::date)
  • Rows Removed by Filter: 102259
20. 40,415.625 40,415.625 ↑ 1.0 1 4,490,625

Index Scan using uq_theater_properties_1 on theater_properties thp (cost=0.56..5.18 rows=1 width=13) (actual time=0.009..0.009 rows=1 loops=4,490,625)

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
21. 1,570.074 4,710.637 ↑ 6.4 650,062 1

Hash (cost=159,235.40..159,235.40 rows=4,135,840 width=36) (actual time=4,710.637..4,710.637 rows=650,062 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 27519kB
22. 3,140.563 3,140.563 ↑ 6.4 650,062 1

Seq Scan on held_back_weekly_titles hb (cost=0.00..159,235.40 rows=4,135,840 width=36) (actual time=0.550..3,140.563 rows=650,062 loops=1)

23.          

SubPlan (forMerge Join)

24. 10,642.252 742,297.077 ↓ 0.0 0 2,660,563

HashAggregate (cost=531.62..531.65 rows=3 width=4) (actual time=0.279..0.279 rows=0 loops=2,660,563)

  • Group Key: (1)
25. 26,605.630 731,654.825 ↓ 0.0 0 2,660,563

Append (cost=2.40..531.61 rows=3 width=4) (actual time=0.275..0.275 rows=0 loops=2,660,563)

26. 10,411.081 542,754.852 ↓ 0.0 0 2,660,563

Nested Loop (cost=2.40..168.72 rows=1 width=4) (actual time=0.204..0.204 rows=0 loops=2,660,563)

27. 13,296.038 532,112.600 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.83..160.12 rows=1 width=0) (actual time=0.200..0.200 rows=0 loops=2,660,563)

28. 39,908.445 518,809.785 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.54..153.06 rows=1 width=8) (actual time=0.195..0.195 rows=0 loops=2,660,563)

  • 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)))
  • Rows Removed by Join Filter: 3
29. 45,229.571 199,542.225 ↑ 1.0 1 2,660,563

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual time=0.057..0.075 rows=1 loops=2,660,563)

  • 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))
  • Rows Removed by Join Filter: 1
30. 37,247.882 122,385.898 ↑ 1.0 1 2,660,563

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual time=0.032..0.046 rows=1 loops=2,660,563)

31. 21,284.504 21,284.504 ↑ 1.0 1 2,660,563

Index Scan using pk_releases on releases r_15 (cost=0.42..8.44 rows=1 width=12) (actual time=0.005..0.008 rows=1 loops=2,660,563)

  • Index Cond: (b.release_id = id)
32. 63,853.512 63,853.512 ↑ 1.0 1 2,660,563

Seq Scan on week_definitions_by_country wd_15 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.024 rows=1 loops=2,660,563)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
33. 31,926.756 31,926.756 ↑ 1.0 1 2,660,563

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 time=0.008..0.012 rows=1 loops=2,660,563)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_15.title_id))
34. 279,359.115 279,359.115 ↑ 43.7 3 2,660,563

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_6 (cost=0.56..127.52 rows=131 width=16) (actual time=0.083..0.105 rows=3 loops=2,660,563)

  • Index Cond: (b.id = booking_id)
35. 6.777 6.777 ↑ 1.0 1 753

Index Scan using pk_users on users u_12 (cost=0.29..7.06 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=753)

  • Index Cond: (id = ft_6.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
36. 231.171 231.171 ↑ 1.0 1 753

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 3012
37. 10,642.252 77,156.327 ↓ 0.0 0 2,660,563

Nested Loop (cost=2.82..177.20 rows=1 width=4) (actual time=0.029..0.029 rows=0 loops=2,660,563)

38. 13,302.815 66,514.075 ↓ 0.0 0 2,660,563

Nested Loop (cost=2.26..168.61 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=2,660,563)

39. 10,642.252 53,211.260 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.97..161.52 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=2,660,563)

  • Join Filter: ((r_16.distributor_id)::text <> (primary_release_8.distributor_id)::text)
40. 13,051.905 42,569.008 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=2,660,563)

  • 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)))
  • Rows Removed by Join Filter: 0
41. 10,300.102 29,266.193 ↓ 0.0 0 2,660,563

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=2,660,563)

  • 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))
  • Rows Removed by Join Filter: 0
42. 12,755.375 18,623.941 ↓ 0.0 0 2,660,563

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=2,660,563)

43. 5,321.126 5,321.126 ↓ 0.0 0 2,660,563

Index Scan using pk_releases on releases r_16 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=2,660,563)

  • Index Cond: (b.secondary_release_id = id)
44. 547.440 547.440 ↑ 1.0 1 22,810

Seq Scan on week_definitions_by_country wd_16 (cost=0.00..5.62 rows=1 width=4) (actual time=0.018..0.024 rows=1 loops=22,810)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
45. 342.150 342.150 ↑ 1.0 1 22,810

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 time=0.009..0.015 rows=1 loops=22,810)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_16.title_id))
46. 250.910 250.910 ↑ 43.7 3 22,810

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_7 (cost=0.56..127.52 rows=131 width=16) (actual time=0.005..0.011 rows=3 loops=22,810)

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

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

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

Index Scan using pk_users on users u_13 (cost=0.29..7.09 rows=1 width=8) (never executed)

  • 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 0

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 0
50. 10,642.252 85,138.016 ↓ 0.0 0 2,660,563

Nested Loop (cost=3.25..185.66 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=2,660,563)

51. 10,642.252 74,495.764 ↓ 0.0 0 2,660,563

Nested Loop (cost=2.68..177.06 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=2,660,563)

52. 10,642.252 63,853.512 ↓ 0.0 0 2,660,563

Nested Loop (cost=2.40..169.97 rows=1 width=8) (actual time=0.024..0.024 rows=0 loops=2,660,563)

  • Join Filter: ((r_17.distributor_id)::text <> (secondary_release_4.distributor_id)::text)
53. 13,302.815 53,211.260 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.97..161.52 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=2,660,563)

  • Join Filter: ((r_17.distributor_id)::text <> (primary_release_9.distributor_id)::text)
54. 10,631.952 39,908.445 ↓ 0.0 0 2,660,563

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=2,660,563)

  • 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)))
  • Rows Removed by Join Filter: 0
55. 10,626.802 29,266.193 ↓ 0.0 0 2,660,563

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=2,660,563)

  • 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))
  • Rows Removed by Join Filter: 0
56. 13,278.095 18,623.941 ↓ 0.0 0 2,660,563

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.007..0.007 rows=0 loops=2,660,563)

57. 5,321.126 5,321.126 ↓ 0.0 0 2,660,563

Index Scan using pk_releases on releases r_17 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=2,660,563)

  • Index Cond: (b.tertiary_release_id = id)
58. 24.720 24.720 ↑ 1.0 1 1,030

Seq Scan on week_definitions_by_country wd_17 (cost=0.00..5.62 rows=1 width=4) (actual time=0.017..0.024 rows=1 loops=1,030)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
59. 15.450 15.450 ↑ 1.0 1 1,030

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 time=0.010..0.015 rows=1 loops=1,030)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_17.title_id))
60. 10.300 10.300 ↑ 43.7 3 1,030

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_8 (cost=0.56..127.52 rows=131 width=16) (actual time=0.004..0.010 rows=3 loops=1,030)

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

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

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

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

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

Index Scan using pk_users on users u_14 (cost=0.29..7.09 rows=1 width=8) (never executed)

  • 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 0

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 0
65. 10,641.472 279,338.640 ↓ 0.0 0 2,660,368

HashAggregate (cost=170.26..170.29 rows=3 width=4) (actual time=0.105..0.105 rows=0 loops=2,660,368)

  • Group Key: (1)
66. 26,603.680 268,697.168 ↓ 0.0 0 2,660,368

Append (cost=2.25..170.25 rows=3 width=4) (actual time=0.101..0.101 rows=0 loops=2,660,368)

67. 10,636.504 74,490.304 ↓ 0.0 0 2,660,368

Nested Loop (cost=2.25..48.29 rows=1 width=4) (actual time=0.028..0.028 rows=0 loops=2,660,368)

68. 12,959.673 63,848.832 ↓ 0.0 0 2,660,368

Nested Loop Left Join (cost=1.69..39.69 rows=1 width=0) (actual time=0.024..0.024 rows=0 loops=2,660,368)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
69. 9,468.328 50,546.992 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual time=0.019..0.019 rows=0 loops=2,660,368)

70. 13,057.435 39,905.520 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual time=0.014..0.015 rows=0 loops=2,660,368)

71. 13,008.554 26,603.680 ↓ 0.0 0 2,660,368

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual time=0.010..0.010 rows=0 loops=2,660,368)

72. 13,301.840 13,301.840 ↓ 0.0 0 2,660,368

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_6 (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=2,660,368)

  • Index Cond: (b.id = booking_id)
73. 293.286 293.286 ↑ 1.0 1 48,881

Index Scan using pk_releases on releases r_18 (cost=0.42..8.44 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=48,881)

  • Index Cond: (b.release_id = id)
74. 244.405 244.405 ↑ 1.0 1 48,881

Index Scan using pk_users on users u_15 (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=48,881)

  • Index Cond: (id = frj_6.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
75. 1,173.144 1,173.144 ↑ 1.0 1 48,881

Seq Scan on week_definitions_by_country wd_18 (cost=0.00..5.62 rows=1 width=4) (actual time=0.016..0.024 rows=1 loops=48,881)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
76. 342.167 342.167 ↑ 1.0 1 48,881

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 time=0.005..0.007 rows=1 loops=48,881)

  • Index Cond: ((theater_id = b.theater_id) AND (title_id = r_18.title_id))
77. 4.968 4.968 ↑ 1.0 1 4

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 16
78. 10,641.472 77,150.672 ↓ 0.0 0 2,660,368

Nested Loop (cost=2.68..56.74 rows=1 width=4) (actual time=0.029..0.029 rows=0 loops=2,660,368)

79. 10,632.799 66,509.200 ↓ 0.0 0 2,660,368

Nested Loop Left Join (cost=2.12..48.15 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=2,660,368)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
80. 13,273.343 55,867.728 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.56..39.29 rows=1 width=24) (actual time=0.021..0.021 rows=0 loops=2,660,368)

81. 10,636.516 42,565.888 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.56..33.66 rows=1 width=20) (actual time=0.016..0.016 rows=0 loops=2,660,368)

82. 10,630.467 31,924.416 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual time=0.012..0.012 rows=0 loops=2,660,368)

  • Join Filter: ((r_19.distributor_id)::text <> (primary_release_10.distributor_id)::text)
  • Rows Removed by Join Filter: 0
83. 13,204.078 21,282.944 ↓ 0.0 0 2,660,368

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=2,660,368)

84. 7,981.104 7,981.104 ↓ 0.0 0 2,660,368

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_7 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=2,660,368)

  • Index Cond: (b.id = booking_id)
85. 97.762 97.762 ↓ 0.0 0 48,881

Index Scan using pk_releases on releases r_19 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=48,881)

  • Index Cond: (b.secondary_release_id = id)
86. 11.005 11.005 ↑ 1.0 1 2,201

Index Scan using pk_releases on releases primary_release_10 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=2,201)

  • Index Cond: (b.release_id = id)
87. 4.956 4.956 ↑ 1.0 1 1,239

Index Scan using pk_users on users u_16 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,239)

  • Index Cond: (id = frj_7.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
88. 28.497 28.497 ↑ 1.0 1 1,239

Seq Scan on week_definitions_by_country wd_19 (cost=0.00..5.62 rows=1 width=4) (actual time=0.018..0.023 rows=1 loops=1,239)

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
89. 8.673 8.673 ↑ 1.0 1 1,239

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 time=0.004..0.007 rows=1 loops=1,239)

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

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 0
91. 13,301.840 90,452.512 ↓ 0.0 0 2,660,368

Nested Loop (cost=3.10..65.20 rows=1 width=4) (actual time=0.034..0.034 rows=0 loops=2,660,368)

92. 10,641.008 77,150.672 ↓ 0.0 0 2,660,368

Nested Loop Left Join (cost=2.54..56.60 rows=1 width=0) (actual time=0.029..0.029 rows=0 loops=2,660,368)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
93. 10,640.196 66,509.200 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual time=0.025..0.025 rows=0 loops=2,660,368)

94. 10,641.240 55,867.728 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual time=0.021..0.021 rows=0 loops=2,660,368)

95. 13,301.540 45,226.256 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual time=0.017..0.017 rows=0 loops=2,660,368)

  • Join Filter: ((r_20.distributor_id)::text <> (secondary_release_5.distributor_id)::text)
  • Rows Removed by Join Filter: 0
96. 10,640.822 31,924.416 ↓ 0.0 0 2,660,368

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual time=0.012..0.012 rows=0 loops=2,660,368)

  • Join Filter: ((r_20.distributor_id)::text <> (primary_release_11.distributor_id)::text)
  • Rows Removed by Join Filter: 0
97. 13,204.078 21,282.944 ↓ 0.0 0 2,660,368

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.008..0.008 rows=0 loops=2,660,368)

98. 7,981.104 7,981.104 ↓ 0.0 0 2,660,368

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_8 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=2,660,368)

  • Index Cond: (b.id = booking_id)
99. 97.762 97.762 ↓ 0.0 0 48,881

Index Scan using pk_releases on releases r_20 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=48,881)

  • Index Cond: (b.tertiary_release_id = id)
100. 0.650 0.650 ↑ 1.0 1 130

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

  • Index Cond: (b.release_id = id)
101. 0.300 0.300 ↑ 1.0 1 75

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

  • Index Cond: (b.secondary_release_id = id)
102. 0.232 0.232 ↑ 1.0 1 58

Index Scan using pk_users on users u_17 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=58)

  • Index Cond: (id = frj_8.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b.booking_date >= '2017-05-19'::date))
103. 1.276 1.276 ↑ 1.0 1 58

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

  • Filter: ((b.booking_date >= from_date) AND (b.booking_date <= to_date) AND ((th.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
104. 0.464 0.464 ↓ 2.0 2 58

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 time=0.005..0.008 rows=2 loops=58)

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

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

  • Index Cond: ((theater_id = b.theater_id) AND (as_of_date = b.booking_date))
  • Heap Fetches: 0
106. 10.303 83.881 ↓ 1.0 4,709 1

Hash (cost=2,267.92..2,267.92 rows=4,535 width=8) (actual time=83.881..83.881 rows=4,709 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 248kB
107. 58.416 73.578 ↓ 1.0 4,709 1

Bitmap Heap Scan on queued_resends qr (cost=155.57..2,267.92 rows=4,535 width=8) (actual time=38.951..73.578 rows=4,709 loops=1)

  • Recheck Cond: (time_sent IS NULL)
  • Heap Blocks: exact=173
108. 15.162 15.162 ↓ 1.1 5,058 1

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..154.43 rows=4,535 width=0) (actual time=15.162..15.162 rows=5,058 loops=1)

  • Index Cond: (time_sent IS NULL)
109. 0.048 111,172.781 ↑ 9.3 3 1

Hash Anti Join (cost=1,625,571.07..2,367,411.48 rows=28 width=51) (actual time=105,986.765..111,172.781 rows=3 loops=1)

  • Hash Cond: (b_1.id = qr_1.booking_id)
110. 7,227.998 111,092.077 ↑ 9.3 3 1

Merge Join (cost=1,623,246.47..2,365,086.52 rows=28 width=51) (actual time=105,906.076..111,092.077 rows=3 loops=1)

  • Merge Cond: ((th_1.id = b_1.theater_id) AND ((th_1.country_id)::text = (wd_1.country_id)::text))
  • Join Filter: ((SubPlan 3) OR (SubPlan 4))
  • Rows Removed by Join Filter: 21513
111. 552.968 2,472.135 ↑ 23.0 116,956 1

Sort (cost=611,710.12..618,435.10 rows=2,689,991 width=11) (actual time=2,212.937..2,472.135 rows=116,956 loops=1)

  • Sort Key: th_1.id, th_1.country_id
  • Sort Method: quicksort Memory: 8652kB
112. 1,919.167 1,919.167 ↑ 22.6 119,022 1

Seq Scan on theaters th_1 (cost=0.00..278,457.75 rows=2,689,991 width=11) (actual time=10.312..1,919.167 rows=119,022 loops=1)

  • Filter: (id <> ALL ('{936121,936883,937400,940621,940665,940666,945089,945310,975437,975438,994803,1042894,1054028,1058439,1058485,1061071,1063880}'::bigint[]))
  • Rows Removed by Filter: 17
113. 21,892.114 90,247.022 ↓ 22.3 3,050,184 1

Sort (cost=1,011,335.99..1,011,677.18 rows=136,475 width=86) (actual time=83,499.811..90,247.022 rows=3,050,184 loops=1)

  • Sort Key: b_1.theater_id, wd_1.country_id
  • Sort Method: external sort Disk: 292568kB
114. 25,378.255 68,354.908 ↓ 22.3 3,050,184 1

Hash Left Join (cost=285,493.31..999,695.85 rows=136,475 width=86) (actual time=7,781.232..68,354.908 rows=3,050,184 loops=1)

  • 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))
  • Rows Removed by Join Filter: 3700358
  • 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
  • Rows Removed by Filter: 35836
115. 14,018.274 38,579.583 ↓ 11.3 3,086,020 1

Nested Loop (cost=31,908.31..630,139.36 rows=272,950 width=98) (actual time=3,187.718..38,579.583 rows=3,086,020 loops=1)

  • Join Filter: ((b_1.booking_date >= wd_1.from_date) AND (b_1.booking_date <= wd_1.to_date))
  • Rows Removed by Join Filter: 220430
116. 1.359 1.359 ↑ 1.0 150 1

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 time=0.028..1.359 rows=150 loops=1)

117. 7,041.399 24,559.950 ↓ 1.3 22,043 150

Materialize (cost=31,908.16..587,040.63 rows=16,377 width=91) (actual time=21.254..163.733 rows=22,043 loops=150)

118. 444.947 17,518.551 ↓ 1.3 22,043 1

Merge Join (cost=31,908.16..586,958.75 rows=16,377 width=91) (actual time=3,187.660..17,518.551 rows=22,043 loops=1)

  • 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)))
  • Rows Removed by Join Filter: 55
119. 1,046.212 13,826.818 ↑ 40.4 143,536 1

Nested Loop (cost=1.13..216,176,109.36 rows=5,804,599 width=53) (actual time=0.967..13,826.818 rows=143,536 loops=1)

120. 6,748.524 6,748.524 ↑ 178.4 143,621 1

Index Scan using ix_bookings_secondary_release_1 on bookings b_1 (cost=0.56..83,377,538.27 rows=25,620,101 width=44) (actual time=0.027..6,748.524 rows=143,621 loops=1)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
  • Rows Removed by Filter: 235
121. 6,032.082 6,032.082 ↑ 1.0 1 143,621

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
122. 74.589 3,246.786 ↓ 1.1 27,333 1

Sort (cost=31,904.70..31,964.17 rows=23,791 width=38) (actual time=3,186.656..3,246.786 rows=27,333 loops=1)

  • Sort Key: r_1.id
  • Sort Method: quicksort Memory: 615kB
123. 285.598 3,172.197 ↑ 4.4 5,407 1

Hash Right Join (cost=30,155.69..30,175.31 rows=23,791 width=38) (actual time=2,888.833..3,172.197 rows=5,407 loops=1)

  • 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[])))
  • Rows Removed by Filter: 601258
124. 0.045 0.045 ↑ 1.0 10 1

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

125. 1,458.229 2,886.554 ↑ 1.0 606,665 1

Hash (cost=21,037.55..21,037.55 rows=607,876 width=31) (actual time=2,886.554..2,886.554 rows=606,665 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 45454kB
126. 1,428.325 1,428.325 ↑ 1.0 606,665 1

Seq Scan on releases r_1 (cost=0.00..21,037.55 rows=607,876 width=31) (actual time=0.012..1,428.325 rows=606,665 loops=1)

  • Filter: (release_date <= '2019-10-03'::date)
  • Rows Removed by Filter: 102259
127. 1,579.031 4,397.070 ↑ 6.4 650,062 1

Hash (cost=159,235.40..159,235.40 rows=4,135,840 width=36) (actual time=4,397.070..4,397.070 rows=650,062 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 27519kB
128. 2,818.039 2,818.039 ↑ 6.4 650,062 1

Seq Scan on held_back_weekly_titles hb_1 (cost=0.00..159,235.40 rows=4,135,840 width=36) (actual time=0.032..2,818.039 rows=650,062 loops=1)

129.          

SubPlan (forMerge Join)

130. 107.580 8,520.336 ↓ 0.0 0 21,516

HashAggregate (cost=531.62..531.65 rows=3 width=4) (actual time=0.396..0.396 rows=0 loops=21,516)

  • Group Key: (1)
131. 193.644 8,412.756 ↓ 0.0 0 21,516

Append (cost=2.40..531.61 rows=3 width=4) (actual time=0.391..0.391 rows=0 loops=21,516)

132. 107.472 4,927.164 ↓ 0.0 0 21,516

Nested Loop (cost=2.40..168.72 rows=1 width=4) (actual time=0.229..0.229 rows=0 loops=21,516)

133. 86.028 4,819.584 ↓ 0.0 0 21,516

Nested Loop (cost=1.83..160.12 rows=1 width=0) (actual time=0.224..0.224 rows=0 loops=21,516)

134. 322.740 4,733.520 ↓ 0.0 0 21,516

Nested Loop (cost=1.54..153.06 rows=1 width=8) (actual time=0.220..0.220 rows=0 loops=21,516)

  • 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)))
  • Rows Removed by Join Filter: 3
135. 387.288 1,635.216 ↑ 1.0 1 21,516

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual time=0.057..0.076 rows=1 loops=21,516)

  • 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))
  • Rows Removed by Join Filter: 1
136. 301.224 1,054.284 ↑ 1.0 1 21,516

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual time=0.035..0.049 rows=1 loops=21,516)

137. 215.160 215.160 ↑ 1.0 1 21,516

Index Scan using pk_releases on releases r_9 (cost=0.42..8.44 rows=1 width=12) (actual time=0.008..0.010 rows=1 loops=21,516)

  • Index Cond: (b_1.release_id = id)
138. 537.900 537.900 ↑ 1.0 1 21,516

Seq Scan on week_definitions_by_country wd_9 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.025 rows=1 loops=21,516)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
139. 193.644 193.644 ↑ 1.0 1 21,516

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 time=0.007..0.009 rows=1 loops=21,516)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_9.title_id))
140. 2,775.564 2,775.564 ↑ 43.7 3 21,516

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_3 (cost=0.56..127.52 rows=131 width=16) (actual time=0.113..0.129 rows=3 loops=21,516)

  • Index Cond: (b_1.id = booking_id)
141. 0.036 0.036 ↑ 1.0 1 6

Index Scan using pk_users on users u_6 (cost=0.29..7.06 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=6)

  • Index Cond: (id = ft_3.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
142. 0.108 0.108 ↑ 1.0 1 6

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 24
143. 86.010 2,474.340 ↓ 0.0 0 21,516

Nested Loop (cost=2.82..177.20 rows=1 width=4) (actual time=0.115..0.115 rows=0 loops=21,516)

144. 107.556 2,388.276 ↓ 0.0 0 21,516

Nested Loop (cost=2.26..168.61 rows=1 width=0) (actual time=0.111..0.111 rows=0 loops=21,516)

145. 86.040 2,280.696 ↓ 0.0 0 21,516

Nested Loop (cost=1.97..161.52 rows=1 width=8) (actual time=0.106..0.106 rows=0 loops=21,516)

  • Join Filter: ((r_10.distributor_id)::text <> (primary_release_4.distributor_id)::text)
146. 344.256 2,194.632 ↓ 0.0 0 21,516

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.102..0.102 rows=0 loops=21,516)

  • 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)))
  • Rows Removed by Join Filter: 3
147. 387.288 1,613.700 ↑ 1.0 1 21,516

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.056..0.075 rows=1 loops=21,516)

  • 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))
  • Rows Removed by Join Filter: 1
148. 301.224 1,011.252 ↑ 1.0 1 21,516

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.032..0.047 rows=1 loops=21,516)

149. 172.128 172.128 ↑ 1.0 1 21,516

Index Scan using pk_releases on releases r_10 (cost=0.42..8.44 rows=1 width=20) (actual time=0.006..0.008 rows=1 loops=21,516)

  • Index Cond: (b_1.secondary_release_id = id)
150. 537.900 537.900 ↑ 1.0 1 21,516

Seq Scan on week_definitions_by_country wd_10 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.025 rows=1 loops=21,516)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
151. 215.160 215.160 ↑ 1.0 1 21,516

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 time=0.006..0.010 rows=1 loops=21,516)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_10.title_id))
152. 236.676 236.676 ↑ 43.7 3 21,516

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_4 (cost=0.56..127.52 rows=131 width=16) (actual time=0.005..0.011 rows=3 loops=21,516)

  • Index Cond: (b_1.id = booking_id)
153. 0.024 0.024 ↑ 1.0 1 6

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

  • Index Cond: (b_1.release_id = id)
154. 0.024 0.024 ↑ 1.0 1 6

Index Scan using pk_users on users u_7 (cost=0.29..7.09 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=6)

  • Index Cond: (id = ft_4.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
155. 0.054 0.054 ↑ 1.0 1 6

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 24
156. 107.580 817.608 ↓ 0.0 0 21,516

Nested Loop (cost=3.25..185.66 rows=1 width=4) (actual time=0.038..0.038 rows=0 loops=21,516)

157. 86.064 710.028 ↓ 0.0 0 21,516

Nested Loop (cost=2.68..177.06 rows=1 width=0) (actual time=0.033..0.033 rows=0 loops=21,516)

158. 86.064 623.964 ↓ 0.0 0 21,516

Nested Loop (cost=2.40..169.97 rows=1 width=8) (actual time=0.029..0.029 rows=0 loops=21,516)

  • Join Filter: ((r_11.distributor_id)::text <> (secondary_release_2.distributor_id)::text)
159. 107.580 537.900 ↓ 0.0 0 21,516

Nested Loop (cost=1.97..161.52 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=21,516)

  • Join Filter: ((r_11.distributor_id)::text <> (primary_release_5.distributor_id)::text)
160. 94.534 430.320 ↓ 0.0 0 21,516

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=21,516)

  • 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)))
  • Rows Removed by Join Filter: 0
161. 119.608 322.740 ↓ 0.0 0 21,516

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.013..0.015 rows=0 loops=21,516)

  • 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))
  • Rows Removed by Join Filter: 0
162. 100.632 193.644 ↓ 0.0 0 21,516

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.008..0.009 rows=0 loops=21,516)

163. 64.548 64.548 ↓ 0.0 0 21,516

Index Scan using pk_releases on releases r_11 (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=21,516)

  • Index Cond: (b_1.tertiary_release_id = id)
164. 28.464 28.464 ↑ 1.0 1 1,186

Seq Scan on week_definitions_by_country wd_11 (cost=0.00..5.62 rows=1 width=4) (actual time=0.017..0.024 rows=1 loops=1,186)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
165. 9.488 9.488 ↑ 1.0 1 1,186

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 time=0.005..0.008 rows=1 loops=1,186)

  • Index Cond: ((theater_id = b_1.theater_id) AND (title_id = r_11.title_id))
166. 13.046 13.046 ↑ 43.7 3 1,186

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_5 (cost=0.56..127.52 rows=131 width=16) (actual time=0.004..0.011 rows=3 loops=1,186)

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

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

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

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

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

Index Scan using pk_users on users u_8 (cost=0.29..7.09 rows=1 width=8) (never executed)

  • 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 0

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 0
171. 107.565 2,624.586 ↓ 0.0 0 21,513

HashAggregate (cost=170.26..170.29 rows=3 width=4) (actual time=0.122..0.122 rows=0 loops=21,513)

  • Group Key: (1)
172. 215.130 2,517.021 ↓ 0.0 0 21,513

Append (cost=2.25..170.25 rows=3 width=4) (actual time=0.117..0.117 rows=0 loops=21,513)

173. 86.052 752.955 ↓ 0.0 0 21,513

Nested Loop (cost=2.25..48.29 rows=1 width=4) (actual time=0.035..0.035 rows=0 loops=21,513)

174. 116.280 666.903 ↓ 0.0 0 21,513

Nested Loop Left Join (cost=1.69..39.69 rows=1 width=0) (actual time=0.031..0.031 rows=0 loops=21,513)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
175. 120.912 537.825 ↓ 0.0 0 21,513

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual time=0.022..0.025 rows=0 loops=21,513)

176. 116.280 365.721 ↓ 0.0 0 21,513

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual time=0.016..0.017 rows=0 loops=21,513)

177. 116.280 236.643 ↓ 0.0 0 21,513

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual time=0.010..0.011 rows=0 loops=21,513)

178. 107.565 107.565 ↓ 0.0 0 21,513

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_3 (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=21,513)

  • Index Cond: (b_1.id = booking_id)
179. 12.798 12.798 ↑ 1.0 1 2,133

Index Scan using pk_releases on releases r_12 (cost=0.42..8.44 rows=1 width=12) (actual time=0.004..0.006 rows=1 loops=2,133)

  • Index Cond: (b_1.release_id = id)
180. 12.798 12.798 ↑ 1.0 1 2,133

Index Scan using pk_users on users u_9 (cost=0.29..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=2,133)

  • Index Cond: (id = frj_3.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
181. 51.192 51.192 ↑ 1.0 1 2,133

Seq Scan on week_definitions_by_country wd_12 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.024 rows=1 loops=2,133)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
182. 12.798 12.798 ↑ 1.0 1 2,133

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 time=0.005..0.006 rows=1 loops=2,133)

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

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 0
184. 86.052 774.468 ↓ 0.0 0 21,513

Nested Loop (cost=2.68..56.74 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=21,513)

185. 99.431 688.416 ↓ 0.0 0 21,513

Nested Loop Left Join (cost=2.12..48.15 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=21,513)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
186. 121.541 580.851 ↓ 0.0 0 21,513

Nested Loop (cost=1.56..39.29 rows=1 width=24) (actual time=0.025..0.027 rows=0 loops=21,513)

187. 102.917 430.260 ↓ 0.0 0 21,513

Nested Loop (cost=1.56..33.66 rows=1 width=20) (actual time=0.019..0.020 rows=0 loops=21,513)

188. 96.900 322.695 ↓ 0.0 0 21,513

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=21,513)

  • Join Filter: ((r_13.distributor_id)::text <> (primary_release_6.distributor_id)::text)
  • Rows Removed by Join Filter: 0
189. 114.147 215.130 ↓ 0.0 0 21,513

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.009..0.010 rows=0 loops=21,513)

190. 86.052 86.052 ↓ 0.0 0 21,513

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_4 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=21,513)

  • Index Cond: (b_1.id = booking_id)
191. 14.931 14.931 ↑ 1.0 1 2,133

Index Scan using pk_releases on releases r_13 (cost=0.42..8.44 rows=1 width=20) (actual time=0.004..0.007 rows=1 loops=2,133)

  • Index Cond: (b_1.secondary_release_id = id)
192. 10.665 10.665 ↑ 1.0 1 2,133

Index Scan using pk_releases on releases primary_release_6 (cost=0.42..8.44 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=2,133)

  • Index Cond: (b_1.release_id = id)
193. 4.648 4.648 ↑ 1.0 1 1,162

Index Scan using pk_users on users u_10 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,162)

  • Index Cond: (id = frj_4.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
194. 29.050 29.050 ↑ 1.0 1 1,162

Seq Scan on week_definitions_by_country wd_13 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.025 rows=1 loops=1,162)

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
195. 8.134 8.134 ↑ 1.0 1 1,162

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 time=0.005..0.007 rows=1 loops=1,162)

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

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 0
197. 107.565 774.468 ↓ 0.0 0 21,513

Nested Loop (cost=3.10..65.20 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=21,513)

198. 85.478 666.903 ↓ 0.0 0 21,513

Nested Loop Left Join (cost=2.54..56.60 rows=1 width=0) (actual time=0.031..0.031 rows=0 loops=21,513)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
199. 105.433 580.851 ↓ 0.0 0 21,513

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual time=0.027..0.027 rows=0 loops=21,513)

200. 85.724 473.286 ↓ 0.0 0 21,513

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual time=0.022..0.022 rows=0 loops=21,513)

201. 107.105 387.234 ↓ 0.0 0 21,513

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual time=0.018..0.018 rows=0 loops=21,513)

  • Join Filter: ((r_14.distributor_id)::text <> (secondary_release_3.distributor_id)::text)
  • Rows Removed by Join Filter: 0
202. 85.382 279.669 ↓ 0.0 0 21,513

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual time=0.013..0.013 rows=0 loops=21,513)

  • Join Filter: ((r_14.distributor_id)::text <> (primary_release_7.distributor_id)::text)
  • Rows Removed by Join Filter: 0
203. 101.166 193.617 ↓ 0.0 0 21,513

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.009..0.009 rows=0 loops=21,513)

204. 86.052 86.052 ↓ 0.0 0 21,513

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_5 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.004 rows=0 loops=21,513)

  • Index Cond: (b_1.id = booking_id)
205. 6.399 6.399 ↓ 0.0 0 2,133

Index Scan using pk_releases on releases r_14 (cost=0.42..8.44 rows=1 width=20) (actual time=0.002..0.003 rows=0 loops=2,133)

  • Index Cond: (b_1.tertiary_release_id = id)
206. 0.670 0.670 ↑ 1.0 1 134

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

  • Index Cond: (b_1.release_id = id)
207. 0.460 0.460 ↑ 1.0 1 92

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

  • Index Cond: (b_1.secondary_release_id = id)
208. 0.328 0.328 ↑ 1.0 1 82

Index Scan using pk_users on users u_11 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=82)

  • Index Cond: (id = frj_5.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_1.booking_date >= '2017-05-19'::date))
209. 2.132 2.132 ↑ 1.0 1 82

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

  • Filter: ((b_1.booking_date >= from_date) AND (b_1.booking_date <= to_date) AND ((th_1.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
210. 0.574 0.574 ↑ 1.0 1 82

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 time=0.005..0.007 rows=1 loops=82)

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

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

  • Index Cond: ((theater_id = b_1.theater_id) AND (as_of_date = b_1.booking_date))
  • Heap Fetches: 0
212. 10.474 80.656 ↓ 1.0 4,709 1

Hash (cost=2,267.92..2,267.92 rows=4,535 width=8) (actual time=80.656..80.656 rows=4,709 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 248kB
213. 62.719 70.182 ↓ 1.0 4,709 1

Bitmap Heap Scan on queued_resends qr_1 (cost=155.57..2,267.92 rows=4,535 width=8) (actual time=35.347..70.182 rows=4,709 loops=1)

  • Recheck Cond: (time_sent IS NULL)
  • Heap Blocks: exact=173
214. 7.463 7.463 ↓ 1.1 5,058 1

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..154.43 rows=4,535 width=0) (actual time=7.463..7.463 rows=5,058 loops=1)

  • Index Cond: (time_sent IS NULL)
215. 0.006 15,286.392 ↓ 0.0 0 1

Nested Loop Left Join (cost=32,110.35..688,063.41 rows=28 width=51) (actual time=15,286.392..15,286.392 rows=0 loops=1)

  • 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.006 15,286.386 ↓ 0.0 0 1

Merge Join (cost=32,109.80..687,772.39 rows=56 width=63) (actual time=15,286.386..15,286.386 rows=0 loops=1)

  • 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)))
217. 10.197 15,286.380 ↑ 19,917.0 1 1

Nested Loop Anti Join (cost=157.13..4,352,472,523.02 rows=19,917 width=33) (actual time=15,286.380..15,286.380 rows=1 loops=1)

  • Join Filter: (qr_2.booking_id = b_2.id)
  • Rows Removed by Join Filter: 4709
218. 0.328 15,244.307 ↑ 19,919.0 1 1

Nested Loop (cost=1.56..4,351,115,253.79 rows=19,919 width=33) (actual time=15,244.307..15,244.307 rows=1 loops=1)

  • Join Filter: ((b_2.booking_date >= wd_2.from_date) AND (b_2.booking_date <= wd_2.to_date) AND ((th_2.country_id)::text = (wd_2.country_id)::text))
  • Rows Removed by Join Filter: 132
219. 140.005 15,243.045 ↑ 158,769.0 1 1

Nested Loop (cost=1.56..4,350,638,941.91 rows=158,769 width=32) (actual time=15,243.045..15,243.045 rows=1 loops=1)

220. 275.586 3,011.364 ↑ 196.3 29,564 1

Nested Loop (cost=1.13..250,337,315.80 rows=5,804,599 width=53) (actual time=0.080..3,011.364 rows=29,564 loops=1)

221. 325.652 325.652 ↑ 659.1 38,873 1

Index Scan using ix_bookings_tertiary_release_1 on bookings b_2 (cost=0.56..117,538,744.71 rows=25,620,101 width=44) (actual time=0.034..325.652 rows=38,873 loops=1)

  • Filter: ((booking_date <= '2019-10-03'::date) AND CASE WHEN (theater_id = 935840) THEN (booking_date >= '2017-11-17'::date) ELSE true END)
  • Rows Removed by Filter: 75844
222. 2,410.126 2,410.126 ↑ 1.0 1 38,873

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
223. 680.084 12,091.676 ↓ 0.0 0 29,564

Index Scan using pk_theaters on theaters th_2 (cost=0.43..706.38 rows=1 width=11) (actual time=0.409..0.409 rows=0 loops=29,564)

  • 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)))
  • Rows Removed by Filter: 1
224.          

SubPlan (forIndex Scan)

225. 147.820 8,100.536 ↓ 0.0 0 29,564

HashAggregate (cost=531.62..531.65 rows=3 width=4) (actual time=0.274..0.274 rows=0 loops=29,564)

  • Group Key: (1)
226. 266.076 7,952.716 ↓ 0.0 0 29,564

Append (cost=2.40..531.61 rows=3 width=4) (actual time=0.269..0.269 rows=0 loops=29,564)

227. 118.237 4,848.496 ↓ 0.0 0 29,564

Nested Loop (cost=2.40..168.72 rows=1 width=4) (actual time=0.164..0.164 rows=0 loops=29,564)

228. 147.800 4,730.240 ↓ 0.0 0 29,564

Nested Loop (cost=1.83..160.12 rows=1 width=0) (actual time=0.160..0.160 rows=0 loops=29,564)

229. 502.588 4,582.420 ↓ 0.0 0 29,564

Nested Loop (cost=1.54..153.06 rows=1 width=8) (actual time=0.155..0.155 rows=0 loops=29,564)

  • 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)))
  • Rows Removed by Join Filter: 4
230. 473.024 2,276.428 ↑ 1.0 1 29,564

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=8) (actual time=0.058..0.077 rows=1 loops=29,564)

  • 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))
  • Rows Removed by Join Filter: 0
231. 413.896 1,419.072 ↑ 1.0 1 29,564

Nested Loop (cost=0.42..14.08 rows=1 width=16) (actual time=0.034..0.048 rows=1 loops=29,564)

232. 266.076 266.076 ↑ 1.0 1 29,564

Index Scan using pk_releases on releases r_3 (cost=0.42..8.44 rows=1 width=12) (actual time=0.006..0.009 rows=1 loops=29,564)

  • Index Cond: (b_2.release_id = id)
233. 739.100 739.100 ↑ 1.0 1 29,564

Seq Scan on week_definitions_by_country wd_3 (cost=0.00..5.62 rows=1 width=4) (actual time=0.019..0.025 rows=1 loops=29,564)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
234. 384.332 384.332 ↑ 1.0 1 29,564

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 time=0.011..0.013 rows=1 loops=29,564)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_3.title_id))
235. 1,803.404 1,803.404 ↑ 32.8 4 29,564

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft (cost=0.56..127.52 rows=131 width=16) (actual time=0.038..0.061 rows=4 loops=29,564)

  • Index Cond: (b_2.id = booking_id)
236. 0.020 0.020 ↑ 1.0 1 1

Index Scan using pk_users on users u (cost=0.29..7.06 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (id = ft.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
237. 0.019 0.019 ↑ 1.0 1 1

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 4
238. 147.820 1,359.944 ↓ 0.0 0 29,564

Nested Loop (cost=2.82..177.20 rows=1 width=4) (actual time=0.046..0.046 rows=0 loops=29,564)

239. 118.256 1,212.124 ↓ 0.0 0 29,564

Nested Loop (cost=2.26..168.61 rows=1 width=0) (actual time=0.041..0.041 rows=0 loops=29,564)

240. 118.256 1,093.868 ↓ 0.0 0 29,564

Nested Loop (cost=1.97..161.52 rows=1 width=8) (actual time=0.037..0.037 rows=0 loops=29,564)

  • Join Filter: ((r_4.distributor_id)::text <> (primary_release.distributor_id)::text)
241. 199.636 975.612 ↓ 0.0 0 29,564

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.033..0.033 rows=0 loops=29,564)

  • 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)))
  • Rows Removed by Join Filter: 1
242. 217.756 709.536 ↓ 0.0 0 29,564

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.019..0.024 rows=0 loops=29,564)

  • 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))
  • Rows Removed by Join Filter: 0
243. 174.204 443.460 ↓ 0.0 0 29,564

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.012..0.015 rows=0 loops=29,564)

244. 118.256 118.256 ↓ 0.0 0 29,564

Index Scan using pk_releases on releases r_4 (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.004 rows=0 loops=29,564)

  • Index Cond: (b_2.secondary_release_id = id)
245. 151.000 151.000 ↑ 1.0 1 6,040

Seq Scan on week_definitions_by_country wd_4 (cost=0.00..5.62 rows=1 width=4) (actual time=0.018..0.025 rows=1 loops=6,040)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
246. 48.320 48.320 ↑ 1.0 1 6,040

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 time=0.006..0.008 rows=1 loops=6,040)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_4.title_id))
247. 66.440 66.440 ↑ 43.7 3 6,040

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_1 (cost=0.56..127.52 rows=131 width=16) (actual time=0.005..0.011 rows=3 loops=6,040)

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

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

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

Index Scan using pk_users on users u_1 (cost=0.29..7.09 rows=1 width=8) (never executed)

  • 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 0

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 0
251. 118.256 1,478.200 ↓ 0.0 0 29,564

Nested Loop (cost=3.25..185.66 rows=1 width=4) (actual time=0.050..0.050 rows=0 loops=29,564)

252. 147.820 1,359.944 ↓ 0.0 0 29,564

Nested Loop (cost=2.68..177.06 rows=1 width=0) (actual time=0.046..0.046 rows=0 loops=29,564)

253. 118.256 1,212.124 ↓ 0.0 0 29,564

Nested Loop (cost=2.40..169.97 rows=1 width=8) (actual time=0.041..0.041 rows=0 loops=29,564)

  • Join Filter: ((r_5.distributor_id)::text <> (secondary_release.distributor_id)::text)
254. 147.820 1,093.868 ↓ 0.0 0 29,564

Nested Loop (cost=1.97..161.52 rows=1 width=16) (actual time=0.037..0.037 rows=0 loops=29,564)

  • Join Filter: ((r_5.distributor_id)::text <> (primary_release_1.distributor_id)::text)
255. 171.700 946.048 ↓ 0.0 0 29,564

Nested Loop (cost=1.54..153.06 rows=1 width=16) (actual time=0.032..0.032 rows=0 loops=29,564)

  • 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)))
  • Rows Removed by Join Filter: 1
256. 195.268 709.536 ↓ 0.0 0 29,564

Nested Loop Left Join (cost=0.98..22.92 rows=1 width=16) (actual time=0.020..0.024 rows=0 loops=29,564)

  • 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))
  • Rows Removed by Join Filter: 0
257. 207.468 473.024 ↓ 0.0 0 29,564

Nested Loop (cost=0.42..14.08 rows=1 width=24) (actual time=0.012..0.016 rows=0 loops=29,564)

258. 118.256 118.256 ↓ 0.0 0 29,564

Index Scan using pk_releases on releases r_5 (cost=0.42..8.44 rows=1 width=20) (actual time=0.004..0.004 rows=0 loops=29,564)

  • Index Cond: (b_2.tertiary_release_id = id)
259. 147.300 147.300 ↑ 1.0 1 5,892

Seq Scan on week_definitions_by_country wd_5 (cost=0.00..5.62 rows=1 width=4) (actual time=0.018..0.025 rows=1 loops=5,892)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
260. 41.244 41.244 ↑ 1.0 1 5,892

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 time=0.005..0.007 rows=1 loops=5,892)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_5.title_id))
261. 64.812 64.812 ↑ 43.7 3 5,892

Index Scan using ix_finalized_transactions_boo_13 on finalized_transactions ft_2 (cost=0.56..127.52 rows=131 width=16) (actual time=0.005..0.011 rows=3 loops=5,892)

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

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

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

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

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

Index Scan using pk_users on users u_2 (cost=0.29..7.09 rows=1 width=8) (never executed)

  • 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 0

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 0
266. 118.252 3,311.056 ↓ 0.0 0 29,563

HashAggregate (cost=170.26..170.29 rows=3 width=4) (actual time=0.112..0.112 rows=0 loops=29,563)

  • Group Key: (1)
267. 266.067 3,192.804 ↓ 0.0 0 29,563

Append (cost=2.25..170.25 rows=3 width=4) (actual time=0.108..0.108 rows=0 loops=29,563)

268. 147.815 916.453 ↓ 0.0 0 29,563

Nested Loop (cost=2.25..48.29 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=29,563)

269. 137.063 768.638 ↓ 0.0 0 29,563

Nested Loop Left Join (cost=1.69..39.69 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=29,563)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
270. 140.514 620.823 ↓ 0.0 0 29,563

Nested Loop (cost=1.13..30.84 rows=1 width=24) (actual time=0.020..0.021 rows=0 loops=29,563)

271. 138.599 443.445 ↓ 0.0 0 29,563

Nested Loop (cost=1.13..25.20 rows=1 width=20) (actual time=0.014..0.015 rows=0 loops=29,563)

272. 137.063 295.630 ↓ 0.0 0 29,563

Nested Loop (cost=0.85..16.89 rows=1 width=28) (actual time=0.009..0.010 rows=0 loops=29,563)

273. 147.815 147.815 ↓ 0.0 0 29,563

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj (cost=0.42..8.44 rows=1 width=16) (actual time=0.004..0.005 rows=0 loops=29,563)

  • Index Cond: (b_2.id = booking_id)
274. 10.752 10.752 ↑ 1.0 1 1,536

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

  • Index Cond: (b_2.release_id = id)
275. 9.216 9.216 ↑ 1.0 1 1,536

Index Scan using pk_users on users u_3 (cost=0.29..8.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1,536)

  • Index Cond: (id = frj.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
276. 36.864 36.864 ↑ 1.0 1 1,536

Seq Scan on week_definitions_by_country wd_6 (cost=0.00..5.62 rows=1 width=4) (actual time=0.017..0.024 rows=1 loops=1,536)

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
277. 10.752 10.752 ↑ 1.0 1 1,536

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 time=0.005..0.007 rows=1 loops=1,536)

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

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 0
279. 147.815 946.016 ↓ 0.0 0 29,563

Nested Loop (cost=2.68..56.74 rows=1 width=4) (actual time=0.032..0.032 rows=0 loops=29,563)

280. 115.252 798.201 ↓ 0.0 0 29,563

Nested Loop Left Join (cost=2.12..48.15 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=29,563)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
281. 135.315 679.949 ↓ 0.0 0 29,563

Nested Loop (cost=1.56..39.29 rows=1 width=24) (actual time=0.022..0.023 rows=0 loops=29,563)

282. 145.815 532.134 ↓ 0.0 0 29,563

Nested Loop (cost=1.56..33.66 rows=1 width=20) (actual time=0.017..0.018 rows=0 loops=29,563)

283. 114.452 384.319 ↓ 0.0 0 29,563

Nested Loop (cost=1.27..25.35 rows=1 width=28) (actual time=0.013..0.013 rows=0 loops=29,563)

  • Join Filter: ((r_7.distributor_id)::text <> (primary_release_2.distributor_id)::text)
  • Rows Removed by Join Filter: 0
284. 171.234 266.067 ↓ 0.0 0 29,563

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.008..0.009 rows=0 loops=29,563)

285. 88.689 88.689 ↓ 0.0 0 29,563

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_1 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=29,563)

  • Index Cond: (b_2.id = booking_id)
286. 6.144 6.144 ↓ 0.0 0 1,536

Index Scan using pk_releases on releases r_7 (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.004 rows=0 loops=1,536)

  • Index Cond: (b_2.secondary_release_id = id)
287. 3.800 3.800 ↑ 1.0 1 760

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

  • Index Cond: (b_2.release_id = id)
288. 2.000 2.000 ↑ 1.0 1 500

Index Scan using pk_users on users u_4 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=500)

  • Index Cond: (id = frj_1.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
289. 12.500 12.500 ↑ 1.0 1 500

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

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
290. 3.000 3.000 ↑ 1.0 1 500

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 time=0.005..0.006 rows=1 loops=500)

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

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 0
292. 118.252 1,064.268 ↓ 0.0 0 29,563

Nested Loop (cost=3.10..65.20 rows=1 width=4) (actual time=0.036..0.036 rows=0 loops=29,563)

293. 145.645 946.016 ↓ 0.0 0 29,563

Nested Loop Left Join (cost=2.54..56.60 rows=1 width=0) (actual time=0.032..0.032 rows=0 loops=29,563)

  • 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))
  • Rows Removed by Join Filter: 0
  • 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)))
  • Rows Removed by Filter: 0
294. 140.065 798.201 ↓ 0.0 0 29,563

Nested Loop (cost=1.98..47.75 rows=1 width=24) (actual time=0.027..0.027 rows=0 loops=29,563)

295. 117.012 650.386 ↓ 0.0 0 29,563

Nested Loop (cost=1.98..42.11 rows=1 width=20) (actual time=0.022..0.022 rows=0 loops=29,563)

296. 145.720 532.134 ↓ 0.0 0 29,563

Nested Loop (cost=1.70..33.80 rows=1 width=28) (actual time=0.018..0.018 rows=0 loops=29,563)

  • Join Filter: ((r_8.distributor_id)::text <> (secondary_release_1.distributor_id)::text)
  • Rows Removed by Join Filter: 0
297. 114.572 384.319 ↓ 0.0 0 29,563

Nested Loop (cost=1.27..25.35 rows=1 width=36) (actual time=0.013..0.013 rows=0 loops=29,563)

  • Join Filter: ((r_8.distributor_id)::text <> (primary_release_3.distributor_id)::text)
  • Rows Removed by Join Filter: 0
298. 171.234 266.067 ↓ 0.0 0 29,563

Nested Loop (cost=0.85..16.89 rows=1 width=36) (actual time=0.008..0.009 rows=0 loops=29,563)

299. 88.689 88.689 ↓ 0.0 0 29,563

Index Scan using ix_finalized_release_justifica_4 on finalized_release_justifications frj_2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=29,563)

  • Index Cond: (b_2.id = booking_id)
300. 6.144 6.144 ↓ 0.0 0 1,536

Index Scan using pk_releases on releases r_8 (cost=0.42..8.44 rows=1 width=20) (actual time=0.003..0.004 rows=0 loops=1,536)

  • Index Cond: (b_2.tertiary_release_id = id)
301. 3.680 3.680 ↑ 1.0 1 736

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

  • Index Cond: (b_2.release_id = id)
302. 2.095 2.095 ↑ 1.0 1 419

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

  • Index Cond: (b_2.secondary_release_id = id)
303. 1.240 1.240 ↑ 1.0 1 310

Index Scan using pk_users on users u_5 (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=310)

  • Index Cond: (id = frj_2.user_id)
  • Filter: (((account_type)::text <> 'POS'::text) OR (b_2.booking_date >= '2017-05-19'::date))
304. 7.750 7.750 ↑ 1.0 1 310

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

  • Filter: ((b_2.booking_date >= from_date) AND (b_2.booking_date <= to_date) AND ((th_2.country_id)::text = (country_id)::text))
  • Rows Removed by Filter: 149
305. 2.170 2.170 ↑ 1.0 1 310

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 time=0.005..0.007 rows=1 loops=310)

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

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

  • Index Cond: ((theater_id = b_2.theater_id) AND (as_of_date = b_2.booking_date))
  • Heap Fetches: 0
307. 0.667 0.934 ↑ 1.1 133 1

Materialize (cost=0.00..5.25 rows=150 width=15) (actual time=0.014..0.934 rows=133 loops=1)

308. 0.267 0.267 ↑ 1.1 133 1

Seq Scan on week_definitions_by_country wd_2 (cost=0.00..4.50 rows=150 width=15) (actual time=0.006..0.267 rows=133 loops=1)

309. 20.548 31.876 ↓ 1.0 4,709 1

Materialize (cost=155.57..2,290.59 rows=4,535 width=8) (actual time=0.366..31.876 rows=4,709 loops=1)

310. 11.106 11.328 ↓ 1.0 4,709 1

Bitmap Heap Scan on queued_resends qr_2 (cost=155.57..2,267.92 rows=4,535 width=8) (actual time=0.359..11.328 rows=4,709 loops=1)

  • Recheck Cond: (time_sent IS NULL)
  • Heap Blocks: exact=173
311. 0.222 0.222 ↓ 1.1 5,058 1

Bitmap Index Scan on ix_queued_resends_time_sent (cost=0.00..154.43 rows=4,535 width=0) (actual time=0.222..0.222 rows=5,058 loops=1)

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

Sort (cost=31,904.70..31,964.17 rows=23,791 width=38) (never executed)

  • Sort Key: r_2.id
313. 0.000 0.000 ↓ 0.0 0

Hash Right Join (cost=30,155.69..30,175.31 rows=23,791 width=38) (never executed)

  • 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[])))
314. 0.000 0.000 ↓ 0.0 0

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

315. 0.000 0.000 ↓ 0.0 0

Hash (cost=21,037.55..21,037.55 rows=607,876 width=31) (never executed)

316. 0.000 0.000 ↓ 0.0 0

Seq Scan on releases r_2 (cost=0.00..21,037.55 rows=607,876 width=31) (never executed)

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

Index Scan using uq_held_back_weekly_titles_1 on held_back_weekly_titles hb_2 (cost=0.56..4.93 rows=1 width=36) (never executed)

  • Index Cond: ((theater_id = b_2.theater_id) AND (title_id = r_2.title_id))
Planning time : 76.340 ms
Execution time : 10,352,807.960 ms