explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MCSD

Settings
# exclusive inclusive rows x rows loops node
1. 1,888.435 4,756,490.496 ↓ 0.0 0 1

Update on tmp_th_xtns_by_theater_rel_week_weurope x1 (cost=10,303.46..39,352,720.03 rows=1,490 width=3,017) (actual time=4,756,490.496..4,756,490.496 rows=0 loops=1)

  • Buffers: shared hit=2098350, local hit=610749363 read=1948 dirtied=5048 written=3113
2. 808.948 4,754,602.061 ↓ 18.1 26,963 1

Hash Join (cost=10,303.46..39,352,720.03 rows=1,490 width=3,017) (actual time=253.273..4,754,602.061 rows=26,963 loops=1)

  • Hash Cond: ((x1.theater_no = x.theater_no) AND (x1.release_no = x.release_no) AND (x1.exhibition_week = x.exhibition_week) AND (COALESCE((x1.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text) = COALESCE((x.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text)) AND ((COALESCE(x1.hide_from_distributors_except, '<NULL>'::character varying))::text = (COALESCE(x.hide_from_distributors_except, '<NULL>'::character varying))::text))
  • Buffers: shared hit=2098350, local hit=610116327
3. 229.864 229.864 ↑ 1.0 50,403 1

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x1 (cost=0.00..7,509.03 rows=50,403 width=2,960) (actual time=0.013..229.864 rows=50,403 loops=1)

  • Buffers: local hit=7005
4. 6.274 67.238 ↓ 61.3 26,963 1

Hash (cost=10,293.56..10,293.56 rows=440 width=48) (actual time=67.238..67.238 rows=26,963 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2394kB
  • Buffers: local hit=7119
5. 15.305 60.964 ↓ 61.3 26,963 1

HashAggregate (cost=10,289.16..10,293.56 rows=440 width=48) (actual time=55.731..60.964 rows=26,963 loops=1)

  • Group Key: x.theater_no, x.release_no, x.exhibition_week, COALESCE((x.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text), (COALESCE(x.hide_from_distributors_except, '<NULL>'::character varying))::text
  • Buffers: local hit=7119
6. 8.208 45.659 ↓ 61.3 26,963 1

Hash Join (cost=364.00..10,283.66 rows=440 width=48) (actual time=3.603..45.659 rows=26,963 loops=1)

  • Hash Cond: ((x.theater_no = o.theater_no) AND (x.release_no = o.release_no))
  • Join Filter: (x.exhibition_week >= o.min_exhibition_week)
  • Rows Removed by Join Filter: 662
  • Buffers: local hit=7119
7. 35.089 35.089 ↑ 1.0 50,403 1

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x (cost=0.00..7,509.03 rows=50,403 width=34) (actual time=0.002..35.089 rows=50,403 loops=1)

  • Buffers: local hit=7005
8. 1.291 2.362 ↑ 1.0 10,000 1

Hash (cost=214.00..214.00 rows=10,000 width=22) (actual time=2.361..2.362 rows=10,000 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 675kB
  • Buffers: local hit=114
9. 1.071 1.071 ↑ 1.0 10,000 1

Seq Scan on tmp_th_out_of_date_xtns_rebuilding_weurope o (cost=0.00..214.00 rows=10,000 width=22) (actual time=0.006..1.071 rows=10,000 loops=1)

  • Buffers: local hit=114
10.          

SubPlan (for Hash Join)

11. 377.482 1,584,022.324 ↑ 1.0 1 26,963

Aggregate (cost=8,799.58..8,799.59 rows=1 width=8) (actual time=58.748..58.748 rows=1 loops=26,963)

  • Buffers: shared hit=699450, local hit=203367401
12. 82.634 1,583,644.842 ↓ 3.0 3 26,963

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=85) (actual time=45.370..58.734 rows=3 loops=26,963)

  • Join Filter: ((x2.theater_no = x3.theater_no) AND (x2.release_no = x3.release_no) AND (x2.exhibition_week = th_week(r3.release_date, w.day1, w.rollback_date)))
  • Buffers: shared hit=699450, local hit=203367401
13. 674.075 1,583,159.508 ↓ 3.0 3 26,963

Nested Loop (cost=0.42..8,781.58 rows=1 width=89) (actual time=45.354..58.716 rows=3 loops=26,963)

  • Join Filter: ((w.country_id)::text = (r.country_id)::text)
  • Rows Removed by Join Filter: 424
  • Buffers: shared hit=591342, local hit=203367401
14. 134.815 134.815 ↑ 1.0 1 26,963

Index Scan using ti_releases_pkey on ti_releases r (cost=0.42..2.44 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Buffers: shared hit=108102
15. 2,213.758 1,582,350.618 ↓ 25.1 427 26,963

Nested Loop (cost=0.00..8,778.93 rows=17 width=83) (actual time=45.343..58.686 rows=427 loops=26,963)

  • Join Filter: ((x2.exhibition_week >= w.from_date) AND (x2.exhibition_week <= w.to_date))
  • Rows Removed by Join Filter: 30
  • Buffers: shared hit=483240, local hit=203367401
16. 1,579,492.540 1,579,492.540 ↓ 3.0 3 26,963

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2 (cost=0.00..8,769.10 rows=1 width=72) (actual time=45.337..58.580 rows=3 loops=26,963)

  • Filter: ((NOT is_intermediate_value) AND (x1.exhibition_week >= exhibition_week) AND (release_no = x1.release_no) AND (x1.theater_no = theater_no) AND ((x1.is_spt_prerelease_rev)::text = (is_spt_prerelease_rev)::text) AND ((COALESCE(x1.hide_from_distributors_except, '<NULL>'::character varying))::text = (COALESCE(hide_from_distributors_except, '<NULL>'::character varying))::text) AND (COALESCE((x1.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text) = COALESCE((hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text)))
  • Rows Removed by Filter: 50400
  • Buffers: local hit=203367401
17. 644.320 644.320 ↑ 1.0 153 80,540

Seq Scan on week_definitions_by_country w (cost=0.00..7.53 rows=153 width=19) (actual time=0.001..0.008 rows=153 loops=80,540)

  • Buffers: shared hit=483240
18. 80.540 402.700 ↓ 0.0 0 80,540

GroupAggregate (cost=17.31..17.34 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=80,540)

  • Group Key: x3.theater_no, x3.release_no, r3.release_date
  • Buffers: shared hit=108108
19. 79.493 322.160 ↓ 0.0 0 80,540

Sort (cost=17.31..17.31 rows=1 width=34) (actual time=0.004..0.004 rows=0 loops=80,540)

  • Sort Key: r3.release_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=108108
20. 26.963 242.667 ↓ 0.0 0 26,963

Nested Loop (cost=2.08..17.30 rows=1 width=34) (actual time=0.009..0.009 rows=0 loops=26,963)

  • Buffers: shared hit=108108
21. 26.963 215.704 ↓ 0.0 0 26,963

Nested Loop (cost=1.40..14.60 rows=1 width=40) (actual time=0.008..0.008 rows=0 loops=26,963)

  • Join Filter: ((x3.exhibition_date >= w_1.from_date) AND (x3.exhibition_date <= w_1.to_date))
  • Buffers: shared hit=108108
22. 26.963 188.741 ↓ 0.0 0 26,963

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual time=0.007..0.007 rows=0 loops=26,963)

  • Buffers: shared hit=108108
23. 26.963 161.778 ↓ 0.0 0 26,963

Nested Loop (cost=0.84..9.98 rows=1 width=39) (actual time=0.006..0.006 rows=0 loops=26,963)

  • Buffers: shared hit=108108
24. 26.814 134.815 ↓ 0.0 0 26,963

Nested Loop (cost=0.42..7.54 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=26,963)

  • Join Filter: ((x3.exhibition_date <= (r3.release_date - '1 day'::interval)) AND (x3.exhibition_date >= (r3.release_date - (((r3.num_prerelease_days_to_include_in_cume)::text || ' day'::text))::interval)) AND ((x3.exhibition_date < (r3.release_date - (((r3.num_prerelease_days_to_include)::text || ' day'::text))::interval)) OR (x3.exhibition_date > (r3.release_date - '1 day'::interval))))
  • Buffers: shared hit=108108
25. 107.852 107.852 ↓ 0.0 0 26,963

Index Scan using ti_releases_pkey on ti_releases r3 (cost=0.42..2.45 rows=1 width=23) (actual time=0.004..0.004 rows=0 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=108102
26. 0.033 0.149 ↓ 0.0 0 1

Append (cost=0.00..4.93 rows=3 width=24) (actual time=0.148..0.149 rows=0 loops=1)

  • Buffers: shared hit=6
27. 0.030 0.030 ↓ 0.0 0 1

Seq Scan on th_xtns x3 (cost=0.00..0.00 rows=1 width=24) (actual time=0.030..0.030 rows=0 loops=1)

  • Filter: ((exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date) AND (theater_no = x1.theater_no) AND (release_no = x1.release_no))
28. 0.057 0.057 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170201 on th_xtns_m20170201 x3_1 (cost=0.43..2.46 rows=1 width=24) (actual time=0.057..0.057 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
29. 0.029 0.029 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170301 on th_xtns_m20170301 x3_2 (cost=0.43..2.46 rows=1 width=24) (actual time=0.029..0.029 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
30. 0.000 0.000 ↓ 0.0 0

Index Scan using th_theaters_pkey on th_theaters t (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (theater_no = x1.theater_no)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_th_primary_theaters_pri_th_no on th_primary_theaters pt (cost=0.41..2.43 rows=1 width=8) (never executed)

  • Index Cond: (primary_theater_no = t.primary_theater_no)
32. 0.000 0.000 ↓ 0.0 0

Index Scan using week_definitions_by_country_pkey on week_definitions_by_country w_1 (cost=0.14..2.16 rows=1 width=19) (never executed)

  • Index Cond: ((country_id)::text = (r3.country_id)::text)
33. 0.000 0.000 ↓ 0.0 0

Index Scan using exchange_rates_by_day_pkey on exchange_rates_by_day e (cost=0.68..2.70 rows=1 width=22) (never executed)

  • Index Cond: ((currency_type_no = x3.currency_type_no) AND (day = th_week((x3.exhibition_date)::timestamp without time zone, w_1.day1, w_1.rollback_date)))
34. 296.593 1,586,125.438 ↑ 1.0 1 26,963

Aggregate (cost=8,799.58..8,799.59 rows=1 width=32) (actual time=58.826..58.826 rows=1 loops=26,963)

  • Buffers: shared hit=699450, local hit=203367401
35. 136.211 1,585,828.845 ↓ 3.0 3 26,963

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=72) (actual time=45.443..58.815 rows=3 loops=26,963)

  • Join Filter: ((x2_1.theater_no = x3_3.theater_no) AND (x2_1.release_no = x3_3.release_no) AND (x2_1.exhibition_week = th_week(r3_1.release_date, w_2.day1, w_2.rollback_date)))
  • Buffers: shared hit=699450, local hit=203367401
36. 674.075 1,585,370.474 ↓ 3.0 3 26,963

Nested Loop (cost=0.42..8,781.58 rows=1 width=76) (actual time=45.427..58.798 rows=3 loops=26,963)

  • Join Filter: ((w_2.country_id)::text = (r_1.country_id)::text)
  • Rows Removed by Join Filter: 424
  • Buffers: shared hit=591342, local hit=203367401
37. 107.852 107.852 ↑ 1.0 1 26,963

Index Scan using ti_releases_pkey on ti_releases r_1 (cost=0.42..2.44 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Buffers: shared hit=108102
38. 2,240.721 1,584,588.547 ↓ 25.1 427 26,963

Nested Loop (cost=0.00..8,778.93 rows=17 width=70) (actual time=45.418..58.769 rows=427 loops=26,963)

  • Join Filter: ((x2_1.exhibition_week >= w_2.from_date) AND (x2_1.exhibition_week <= w_2.to_date))
  • Rows Removed by Join Filter: 30
  • Buffers: shared hit=483240, local hit=203367401
39. 1,581,703.506 1,581,703.506 ↓ 3.0 3 26,963

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2_1 (cost=0.00..8,769.10 rows=1 width=59) (actual time=45.412..58.662 rows=3 loops=26,963)

  • Filter: ((NOT is_intermediate_value) AND (x1.exhibition_week >= exhibition_week) AND (release_no = x1.release_no) AND (x1.theater_no = theater_no) AND ((x1.is_spt_prerelease_rev)::text = (is_spt_prerelease_rev)::text) AND ((COALESCE(x1.hide_from_distributors_except, '<NULL>'::character varying))::text = (COALESCE(hide_from_distributors_except, '<NULL>'::character varying))::text) AND (COALESCE((x1.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text) = COALESCE((hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text)))
  • Rows Removed by Filter: 50400
  • Buffers: local hit=203367401
40. 644.320 644.320 ↑ 1.0 153 80,540

Seq Scan on week_definitions_by_country w_2 (cost=0.00..7.53 rows=153 width=19) (actual time=0.001..0.008 rows=153 loops=80,540)

  • Buffers: shared hit=483240
41. 0.000 322.160 ↓ 0.0 0 80,540

GroupAggregate (cost=17.30..17.33 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=80,540)

  • Group Key: x3_3.theater_no, x3_3.release_no, r3_1.release_date
  • Buffers: shared hit=108108
42. 79.493 322.160 ↓ 0.0 0 80,540

Sort (cost=17.30..17.31 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=80,540)

  • Sort Key: r3_1.release_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=108108
43. 26.963 242.667 ↓ 0.0 0 26,963

Nested Loop Left Join (cost=2.49..17.29 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=26,963)

  • Buffers: shared hit=108108
44. 26.963 215.704 ↓ 0.0 0 26,963

Nested Loop (cost=2.08..16.30 rows=1 width=27) (actual time=0.008..0.008 rows=0 loops=26,963)

  • Buffers: shared hit=108108
45. 26.963 188.741 ↓ 0.0 0 26,963

Nested Loop (cost=1.40..14.60 rows=1 width=43) (actual time=0.007..0.007 rows=0 loops=26,963)

  • Join Filter: ((x3_3.exhibition_date >= w_3.from_date) AND (x3_3.exhibition_date <= w_3.to_date))
  • Buffers: shared hit=108108
46. 26.963 161.778 ↓ 0.0 0 26,963

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual time=0.006..0.006 rows=0 loops=26,963)

  • Buffers: shared hit=108108
47. 0.000 134.815 ↓ 0.0 0 26,963

Nested Loop (cost=0.84..9.98 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=26,963)

  • Buffers: shared hit=108108
48. 26.904 134.815 ↓ 0.0 0 26,963

Nested Loop (cost=0.42..7.54 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=26,963)

  • Join Filter: ((x3_3.exhibition_date <= (r3_1.release_date - '1 day'::interval)) AND (x3_3.exhibition_date >= (r3_1.release_date - (((r3_1.num_prerelease_days_to_include_in_cume)::text || ' day'::text))::interval)) AND ((x3_3.exhibition_date < (r3_1.release_date - (((r3_1.num_prerelease_days_to_include)::text || ' day'::text))::interval)) OR (x3_3.exhibition_date > (r3_1.release_date - '1 day'::interval))))
  • Buffers: shared hit=108108
49. 107.852 107.852 ↓ 0.0 0 26,963

Index Scan using ti_releases_pkey on ti_releases r3_1 (cost=0.42..2.45 rows=1 width=23) (actual time=0.004..0.004 rows=0 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=108102
50. 0.030 0.059 ↓ 0.0 0 1

Append (cost=0.00..4.93 rows=3 width=20) (actual time=0.059..0.059 rows=0 loops=1)

  • Buffers: shared hit=6
51. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on th_xtns x3_3 (cost=0.00..0.00 rows=1 width=20) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date) AND (theater_no = x1.theater_no) AND (release_no = x1.release_no))
52. 0.016 0.016 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170201 on th_xtns_m20170201 x3_4 (cost=0.43..2.46 rows=1 width=20) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
53. 0.011 0.011 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170301 on th_xtns_m20170301 x3_5 (cost=0.43..2.46 rows=1 width=20) (actual time=0.010..0.011 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
54. 0.000 0.000 ↓ 0.0 0

Index Scan using th_theaters_pkey on th_theaters t_1 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (theater_no = x1.theater_no)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_th_primary_theaters_pri_th_no on th_primary_theaters pt_1 (cost=0.41..2.43 rows=1 width=8) (never executed)

  • Index Cond: (primary_theater_no = t_1.primary_theater_no)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using week_definitions_by_country_pkey on week_definitions_by_country w_3 (cost=0.14..2.16 rows=1 width=19) (never executed)

  • Index Cond: ((country_id)::text = (r3_1.country_id)::text)
57. 0.000 0.000 ↓ 0.0 0

Index Only Scan using exchange_rates_by_day_pkey on exchange_rates_by_day e_1 (cost=0.68..1.70 rows=1 width=12) (never executed)

  • Index Cond: ((currency_type_no = x3_3.currency_type_no) AND (day = th_week((x3_3.exhibition_date)::timestamp without time zone, w_3.day1, w_3.rollback_date)))
  • Heap Fetches: 0
58. 0.000 0.000 ↓ 0.0 0

Index Scan using th_cocs_pkey on th_cocs coc (cost=0.41..1.00 rows=1 width=5) (never executed)

  • Index Cond: (coc_no = pt_1.coc_no)
59. 350.519 1,583,348.249 ↑ 1.0 1 26,963

Aggregate (cost=8,799.58..8,799.59 rows=1 width=8) (actual time=58.723..58.723 rows=1 loops=26,963)

  • Buffers: shared hit=699450, local hit=203367401
60. 109.248 1,582,997.730 ↓ 3.0 3 26,963

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=62) (actual time=45.349..58.710 rows=3 loops=26,963)

  • Join Filter: ((x2_2.theater_no = x3_6.theater_no) AND (x2_2.release_no = x3_6.release_no) AND (x2_2.exhibition_week = th_week(r3_2.release_date, w_4.day1, w_4.rollback_date)))
  • Buffers: shared hit=699450, local hit=203367401
61. 701.038 1,582,566.322 ↓ 3.0 3 26,963

Nested Loop (cost=0.42..8,781.58 rows=1 width=66) (actual time=45.334..58.694 rows=3 loops=26,963)

  • Join Filter: ((w_4.country_id)::text = (r_2.country_id)::text)
  • Rows Removed by Join Filter: 424
  • Buffers: shared hit=591342, local hit=203367401
62. 107.852 107.852 ↑ 1.0 1 26,963

Index Scan using ti_releases_pkey on ti_releases r_2 (cost=0.42..2.44 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Buffers: shared hit=108102
63. 2,213.758 1,581,757.432 ↓ 25.1 427 26,963

Nested Loop (cost=0.00..8,778.93 rows=17 width=60) (actual time=45.324..58.664 rows=427 loops=26,963)

  • Join Filter: ((x2_2.exhibition_week >= w_4.from_date) AND (x2_2.exhibition_week <= w_4.to_date))
  • Rows Removed by Join Filter: 30
  • Buffers: shared hit=483240, local hit=203367401
64. 1,578,899.354 1,578,899.354 ↓ 3.0 3 26,963

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2_2 (cost=0.00..8,769.10 rows=1 width=49) (actual time=45.318..58.558 rows=3 loops=26,963)

  • Filter: ((NOT is_intermediate_value) AND (x1.exhibition_week >= exhibition_week) AND (release_no = x1.release_no) AND (x1.theater_no = theater_no) AND ((x1.is_spt_prerelease_rev)::text = (is_spt_prerelease_rev)::text) AND ((COALESCE(x1.hide_from_distributors_except, '<NULL>'::character varying))::text = (COALESCE(hide_from_distributors_except, '<NULL>'::character varying))::text) AND (COALESCE((x1.hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text) = COALESCE((hide_from_cocs_except_coc_no_1)::text, '<NULL>'::text)))
  • Rows Removed by Filter: 50400
  • Buffers: local hit=203367401
65. 644.320 644.320 ↑ 1.0 153 80,540

Seq Scan on week_definitions_by_country w_4 (cost=0.00..7.53 rows=153 width=19) (actual time=0.001..0.008 rows=153 loops=80,540)

  • Buffers: shared hit=483240
66. 0.000 322.160 ↓ 0.0 0 80,540

GroupAggregate (cost=17.31..17.34 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=80,540)

  • Group Key: x3_6.theater_no, x3_6.release_no, r3_2.release_date
  • Buffers: shared hit=108108
67. 106.456 322.160 ↓ 0.0 0 80,540

Sort (cost=17.31..17.31 rows=1 width=34) (actual time=0.004..0.004 rows=0 loops=80,540)

  • Sort Key: r3_2.release_date
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=108108
68. 0.000 215.704 ↓ 0.0 0 26,963

Nested Loop (cost=2.08..17.30 rows=1 width=34) (actual time=0.008..0.008 rows=0 loops=26,963)

  • Buffers: shared hit=108108
69. 26.963 215.704 ↓ 0.0 0 26,963

Nested Loop (cost=1.40..14.60 rows=1 width=40) (actual time=0.008..0.008 rows=0 loops=26,963)

  • Join Filter: ((x3_6.exhibition_date >= w_5.from_date) AND (x3_6.exhibition_date <= w_5.to_date))
  • Buffers: shared hit=108108
70. 26.963 188.741 ↓ 0.0 0 26,963

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual time=0.007..0.007 rows=0 loops=26,963)

  • Buffers: shared hit=108108
71. 26.963 161.778 ↓ 0.0 0 26,963

Nested Loop (cost=0.84..9.98 rows=1 width=39) (actual time=0.006..0.006 rows=0 loops=26,963)

  • Buffers: shared hit=108108
72. 26.894 134.815 ↓ 0.0 0 26,963

Nested Loop (cost=0.42..7.54 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=26,963)

  • Join Filter: ((x3_6.exhibition_date <= (r3_2.release_date - '1 day'::interval)) AND (x3_6.exhibition_date >= (r3_2.release_date - (((r3_2.num_prerelease_days_to_include_in_cume)::text || ' day'::text))::interval)) AND ((x3_6.exhibition_date < (r3_2.release_date - (((r3_2.num_prerelease_days_to_include)::text || ' day'::text))::interval)) OR (x3_6.exhibition_date > (r3_2.release_date - '1 day'::interval))))
  • Buffers: shared hit=108108
73. 107.852 107.852 ↓ 0.0 0 26,963

Index Scan using ti_releases_pkey on ti_releases r3_2 (cost=0.42..2.45 rows=1 width=23) (actual time=0.004..0.004 rows=0 loops=26,963)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=108102
74. 0.026 0.069 ↓ 0.0 0 1

Append (cost=0.00..4.93 rows=3 width=24) (actual time=0.069..0.069 rows=0 loops=1)

  • Buffers: shared hit=6
75. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on th_xtns x3_6 (cost=0.00..0.00 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date) AND (theater_no = x1.theater_no) AND (release_no = x1.release_no))
76. 0.016 0.016 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170201 on th_xtns_m20170201 x3_7 (cost=0.43..2.46 rows=1 width=24) (actual time=0.016..0.016 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
77. 0.025 0.025 ↓ 0.0 0 1

Index Scan using unq_th_xtns_m20170301 on th_xtns_m20170301 x3_8 (cost=0.43..2.46 rows=1 width=24) (actual time=0.025..0.025 rows=0 loops=1)

  • Index Cond: ((theater_no = x1.theater_no) AND (release_no = x1.release_no) AND (exhibition_date >= '2017-02-15'::date) AND (exhibition_date <= '2017-03-16'::date))
  • Buffers: shared hit=3
78. 0.000 0.000 ↓ 0.0 0

Index Scan using th_theaters_pkey on th_theaters t_2 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (theater_no = x1.theater_no)
79. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_th_primary_theaters_pri_th_no on th_primary_theaters pt_2 (cost=0.41..2.43 rows=1 width=8) (never executed)

  • Index Cond: (primary_theater_no = t_2.primary_theater_no)
80. 0.000 0.000 ↓ 0.0 0

Index Scan using week_definitions_by_country_pkey on week_definitions_by_country w_5 (cost=0.14..2.16 rows=1 width=19) (never executed)

  • Index Cond: ((country_id)::text = (r3_2.country_id)::text)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using exchange_rates_by_day_pkey on exchange_rates_by_day e_2 (cost=0.68..2.70 rows=1 width=22) (never executed)

  • Index Cond: ((currency_type_no = x3_6.currency_type_no) AND (day = th_week((x3_6.exhibition_date)::timestamp without time zone, w_5.day1, w_5.rollback_date)))
Planning time : 69.141 ms
Execution time : 4,756,508.837 ms