explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P1IN

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

Update on tmp_th_xtns_by_theater_rel_week_weurope x1 (cost=10,303.49..38,930,339.16 rows=1,474 width=3,018) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,303.49..38,930,339.16 rows=1,474 width=3,018) (actual rows= loops=)

  • 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))
3. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x1 (cost=0.00..7,509.03 rows=50,403 width=2,961) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Hash (cost=10,293.59..10,293.59 rows=440 width=48) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

HashAggregate (cost=10,289.19..10,293.59 rows=440 width=48) (actual rows= loops=)

  • 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
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=364.00..10,283.69 rows=440 width=48) (actual rows= loops=)

  • Hash Cond: ((x.theater_no = o.theater_no) AND (x.release_no = o.release_no))
  • Join Filter: (x.exhibition_week >= o.min_exhibition_week)
7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

Hash (cost=214.00..214.00 rows=10,000 width=22) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

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

10.          

SubPlan (for Hash Join)

11. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,799.58..8,799.59 rows=1 width=8) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=85) (actual rows= loops=)

  • 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)))
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8,781.58 rows=1 width=89) (actual rows= loops=)

  • Join Filter: ((w.country_id)::text = (r.country_id)::text)
14. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r (cost=0.42..2.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..8,778.93 rows=17 width=83) (actual rows= loops=)

  • Join Filter: ((x2.exhibition_week >= w.from_date) AND (x2.exhibition_week <= w.to_date))
16. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2 (cost=0.00..8,769.10 rows=1 width=72) (actual rows= loops=)

  • 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)))
17. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country w (cost=0.00..7.53 rows=153 width=19) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=17.31..17.34 rows=1 width=40) (actual rows= loops=)

  • Group Key: x3.theater_no, x3.release_no, r3.release_date
19. 0.000 0.000 ↓ 0.0

Sort (cost=17.31..17.31 rows=1 width=34) (actual rows= loops=)

  • Sort Key: r3.release_date
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.08..17.30 rows=1 width=34) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.40..14.60 rows=1 width=40) (actual rows= loops=)

  • Join Filter: ((x3.exhibition_date >= w_1.from_date) AND (x3.exhibition_date <= w_1.to_date))
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..9.98 rows=1 width=39) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7.54 rows=1 width=35) (actual rows= loops=)

  • 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))))
25. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r3 (cost=0.42..2.45 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
26. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.93 rows=3 width=24) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • 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))
29. 0.000 0.000 ↓ 0.0

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

  • 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))
30. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (theater_no = x1.theater_no)
31. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: (primary_theater_no = t.primary_theater_no)
32. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: ((country_id)::text = (r3.country_id)::text)
33. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • 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. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=72) (actual rows= loops=)

  • 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)))
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8,781.58 rows=1 width=76) (actual rows= loops=)

  • Join Filter: ((w_2.country_id)::text = (r_1.country_id)::text)
37. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r_1 (cost=0.42..2.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..8,778.93 rows=17 width=70) (actual rows= loops=)

  • Join Filter: ((x2_1.exhibition_week >= w_2.from_date) AND (x2_1.exhibition_week <= w_2.to_date))
39. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2_1 (cost=0.00..8,769.10 rows=1 width=59) (actual rows= loops=)

  • 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)))
40. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country w_2 (cost=0.00..7.53 rows=153 width=19) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=17.30..17.33 rows=1 width=40) (actual rows= loops=)

  • Group Key: x3_3.theater_no, x3_3.release_no, r3_1.release_date
42. 0.000 0.000 ↓ 0.0

Sort (cost=17.30..17.31 rows=1 width=24) (actual rows= loops=)

  • Sort Key: r3_1.release_date
43. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.49..17.29 rows=1 width=24) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.08..16.30 rows=1 width=27) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.40..14.60 rows=1 width=43) (actual rows= loops=)

  • Join Filter: ((x3_3.exhibition_date >= w_3.from_date) AND (x3_3.exhibition_date <= w_3.to_date))
46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..9.98 rows=1 width=35) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7.54 rows=1 width=31) (actual rows= loops=)

  • 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))))
49. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r3_1 (cost=0.42..2.45 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
50. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.93 rows=3 width=20) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • 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))
53. 0.000 0.000 ↓ 0.0

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

  • 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))
54. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (theater_no = x1.theater_no)
55. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: (primary_theater_no = t_1.primary_theater_no)
56. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: ((country_id)::text = (r3_1.country_id)::text)
57. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • 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)))
58. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (coc_no = pt_1.coc_no)
59. 0.000 0.000 ↓ 0.0

Aggregate (cost=8,799.58..8,799.59 rows=1 width=8) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=17.73..8,799.20 rows=1 width=62) (actual rows= loops=)

  • 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)))
61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..8,781.58 rows=1 width=66) (actual rows= loops=)

  • Join Filter: ((w_4.country_id)::text = (r_2.country_id)::text)
62. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r_2 (cost=0.42..2.44 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..8,778.93 rows=17 width=60) (actual rows= loops=)

  • Join Filter: ((x2_2.exhibition_week >= w_4.from_date) AND (x2_2.exhibition_week <= w_4.to_date))
64. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_th_xtns_by_theater_rel_week_weurope x2_2 (cost=0.00..8,769.10 rows=1 width=49) (actual rows= loops=)

  • 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)))
65. 0.000 0.000 ↓ 0.0

Seq Scan on week_definitions_by_country w_4 (cost=0.00..7.53 rows=153 width=19) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=17.31..17.34 rows=1 width=40) (actual rows= loops=)

  • Group Key: x3_6.theater_no, x3_6.release_no, r3_2.release_date
67. 0.000 0.000 ↓ 0.0

Sort (cost=17.31..17.31 rows=1 width=34) (actual rows= loops=)

  • Sort Key: r3_2.release_date
68. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.08..17.30 rows=1 width=34) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.40..14.60 rows=1 width=40) (actual rows= loops=)

  • Join Filter: ((x3_6.exhibition_date >= w_5.from_date) AND (x3_6.exhibition_date <= w_5.to_date))
70. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.26..12.42 rows=1 width=35) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..9.98 rows=1 width=39) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..7.54 rows=1 width=35) (actual rows= loops=)

  • 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))))
73. 0.000 0.000 ↓ 0.0

Index Scan using ti_releases_pkey on ti_releases r3_2 (cost=0.42..2.45 rows=1 width=23) (actual rows= loops=)

  • Index Cond: (release_no = x1.release_no)
  • Filter: (num_prerelease_days_to_include_in_cume > 0)
74. 0.000 0.000 ↓ 0.0

Append (cost=0.00..4.93 rows=3 width=24) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

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

  • 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.000 0.000 ↓ 0.0

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

  • 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))
77. 0.000 0.000 ↓ 0.0

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

  • 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))
78. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (theater_no = x1.theater_no)
79. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: (primary_theater_no = t_2.primary_theater_no)
80. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: ((country_id)::text = (r3_2.country_id)::text)
81. 0.000 0.000 ↓ 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) (actual rows= loops=)

  • Index Cond: ((currency_type_no = x4_6.currency_type_no) AND (day = th_week((x3_6.exhibition_date)::timestamp without time zone, w_5.day1, w_5.rollback_date)))