explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eRD1 : Optimization for: plan #RrK0

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 192.860 12,267.483 ↓ 0.0 0 1

Insert on tmp_th_xtns_by_theater_rel_week_weurope (cost=157,581.73..158,257.61 rows=62 width=6,968) (actual time=12,267.483..12,267.483 rows=0 loops=1)

2. 733.315 12,074.623 ↓ 904.0 56,045 1

Subquery Scan on *SELECT* (cost=157,580.73..158,257.61 rows=62 width=6,968) (actual time=6,710.845..12,074.623 rows=56,045 loops=1)

3. 4,614.618 11,341.308 ↓ 904.0 56,045 1

GroupAggregate (cost=157,580.73..158,246.30 rows=62 width=773) (actual time=6,710.775..11,341.308 rows=56,045 loops=1)

  • Group Key: x.theater_no, x.release_no, (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date)), (CASE WHEN (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date) < w.from_date) THEN true ELSE false END), (CASE WHEN (spt_days.exhibition_date IS NOT NULL) THEN 'Y'::text ELSE 'N'::text END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.coc_no WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.coc_no ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.associated_coc_no_1 WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.associated_coc_no_1 ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.associated_coc_no_2 WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.associated_coc_no_2 ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_dists)::text = 'Y'::text) THEN r.distributor_id WHEN (spt_days.exhibition_date IS NOT NULL) THEN r.distributor_id ELSE NULL::character varying END), mtd.multi_feature_no
4. 153.552 6,726.690 ↓ 3,833.1 237,650 1

Sort (cost=157,580.73..157,580.88 rows=62 width=268) (actual time=6,710.048..6,726.690 rows=237,650 loops=1)

  • Sort Key: x.theater_no, x.release_no, (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date)), (CASE WHEN (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date) < w.from_date) THEN true ELSE false END), (CASE WHEN (spt_days.exhibition_date IS NOT NULL) THEN 'Y'::text ELSE 'N'::text END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.coc_no WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.coc_no ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.associated_coc_no_1 WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.associated_coc_no_1 ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_cocs)::text = 'Y'::text) THEN pt.associated_coc_no_2 WHEN (spt_days.exhibition_date IS NOT NULL) THEN pt.associated_coc_no_2 ELSE NULL::integer END), (CASE WHEN ((pt.hide_xtns_from_other_dists)::text = 'Y'::text) THEN r.distributor_id WHEN (spt_days.exhibition_date IS NOT NULL) THEN r.distributor_id ELSE NULL::character varying END), mtd.multi_feature_no
  • Sort Method: quicksort Memory: 43321kB
5. 1,407.413 6,573.138 ↓ 3,833.1 237,650 1

Hash Left Join (cost=137,653.63..157,578.88 rows=62 width=268) (actual time=754.851..6,573.138 rows=237,650 loops=1)

  • Hash Cond: ((x.theater_no = pre_release_rev_to_include_in_cume_x.theater_no) AND (x.release_no = pre_release_rev_to_include_in_cume_x.release_no))
  • Join Filter: (x.exhibition_date = pre_release_rev_to_include_in_cume_x.exhibition_date)
6. 62.923 4,845.552 ↓ 3,833.1 237,650 1

Hash Left Join (cost=76,586.25..96,479.10 rows=62 width=179) (actual time=434.605..4,845.552 rows=237,650 loops=1)

  • Hash Cond: ((x.theater_no = more_x.theater_no) AND (x.release_no = more_x.release_no))
  • Join Filter: (x.exhibition_date = more_x.exhibition_date)
7. 90.973 4,376.651 ↓ 3,833.1 237,650 1

Nested Loop Left Join (cost=5,456.38..25,348.90 rows=62 width=147) (actual time=28.604..4,376.651 rows=237,650 loops=1)

  • Join Filter: ((x.theater_no = mtd.theater_no) AND (x.exhibition_date = mtd.exhibition_date) AND (x.release_no = r_2.release_no))
8. 141.935 4,285.678 ↓ 3,833.1 237,650 1

Nested Loop Left Join (cost=2,381.76..19,091.79 rows=62 width=135) (actual time=7.779..4,285.678 rows=237,650 loops=1)

  • Filter: ((x.exhibition_date >= r.release_date) OR (spt_days.exhibition_date < r.release_date) OR r.show_pre_release_grosses)
  • Rows Removed by Filter: 3
9. 130.652 3,906.090 ↓ 2,898.2 237,653 1

Nested Loop (cost=2,381.34..19,053.82 rows=82 width=127) (actual time=7.742..3,906.090 rows=237,653 loops=1)

10. 139.786 3,537.785 ↓ 2,898.2 237,653 1

Nested Loop (cost=2,381.05..19,026.26 rows=82 width=111) (actual time=7.723..3,537.785 rows=237,653 loops=1)

11. 632.758 3,160.346 ↓ 2,898.2 237,653 1

Nested Loop (cost=2,380.63..18,987.74 rows=82 width=111) (actual time=7.708..3,160.346 rows=237,653 loops=1)

12. 1,411.406 2,052.282 ↓ 2,863.3 237,653 1

Hash Join (cost=2,379.95..18,764.73 rows=83 width=105) (actual time=7.666..2,052.282 rows=237,653 loops=1)

  • Hash Cond: ((r.country_id)::text = (c.country_id)::text)
  • Join Filter: ((x.exhibition_date <= w.to_date) AND (x.exhibition_date >= (w.from_date - '7 days'::interval)) AND (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date) >= o.min_exhibition_week) AND (th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date) <= o.max_exhibition_week))
  • Rows Removed by Join Filter: 34
13. 3.472 640.705 ↓ 30.2 237,687 1

Nested Loop (cost=2,362.07..15,163.52 rows=7,882 width=82) (actual time=7.447..640.705 rows=237,687 loops=1)

14. 78.587 161.859 ↓ 30.1 237,687 1

Merge Join (cost=2,361.64..10,047.31 rows=7,889 width=62) (actual time=7.434..161.859 rows=237,687 loops=1)

  • Merge Cond: ((x.theater_no = o.theater_no) AND (x.release_no = o.release_no))
15. 61.555 61.555 ↑ 1.0 237,687 1

Index Scan using idx_tmp_ood_xtns_44244980_rel_th_exh on tmp_ood_xtns_44244980 x (cost=0.42..6,231.73 rows=237,687 width=38) (actual time=0.040..61.555 rows=237,687 loops=1)

16. 17.252 21.717 ↓ 9.5 237,687 1

Sort (cost=2,361.21..2,423.71 rows=25,000 width=24) (actual time=7.387..21.717 rows=237,687 loops=1)

  • Sort Key: o.theater_no, o.release_no
  • Sort Method: quicksort Memory: 2722kB
17. 4.465 4.465 ↑ 1.0 25,000 1

Seq Scan on tmp_th_out_of_date_xtns_rebuilding_weurope o (cost=0.00..535.00 rows=25,000 width=24) (actual time=0.034..4.465 rows=25,000 loops=1)

18. 475.374 475.374 ↑ 1.0 1 237,687

Index Scan using ti_releases_pkey on ti_releases r (cost=0.42..0.65 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=237,687)

  • Index Cond: (release_no = x.release_no)
  • Filter: ((should_hide_flash_xtns)::text = 'N'::text)
19. 0.030 0.171 ↑ 1.0 153 1

Hash (cost=15.97..15.97 rows=153 width=52) (actual time=0.171..0.171 rows=153 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
20. 0.054 0.141 ↑ 1.0 153 1

Hash Join (cost=8.03..15.97 rows=153 width=52) (actual time=0.085..0.141 rows=153 loops=1)

  • Hash Cond: ((w.country_id)::text = (c.country_id)::text)
21. 0.019 0.019 ↑ 1.0 153 1

Seq Scan on week_definitions_by_country w (cost=0.00..7.53 rows=153 width=47) (actual time=0.005..0.019 rows=153 loops=1)

22. 0.021 0.068 ↑ 1.0 179 1

Hash (cost=5.79..5.79 rows=179 width=5) (actual time=0.068..0.068 rows=179 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
23. 0.047 0.047 ↑ 1.0 179 1

Seq Scan on ti_countries c (cost=0.00..5.79 rows=179 width=5) (actual time=0.005..0.047 rows=179 loops=1)

24. 475.306 475.306 ↑ 1.0 1 237,653

Index Scan using exchange_rates_by_day_pkey on exchange_rates_by_day e (cost=0.68..2.69 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=237,653)

  • Index Cond: ((currency_type_no = x.currency_type_no) AND (day = th_week((x.exhibition_date)::timestamp without time zone, w.day1, w.rollback_date)))
25. 237.653 237.653 ↑ 1.0 1 237,653

Index Scan using th_theaters_pkey on th_theaters t (cost=0.42..0.47 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=237,653)

  • Index Cond: (theater_no = x.theater_no)
26. 237.653 237.653 ↑ 1.0 1 237,653

Index Scan using idx_th_primary_theaters_pri_th_no on th_primary_theaters pt (cost=0.29..0.34 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=237,653)

  • Index Cond: (primary_theater_no = t.primary_theater_no)
27. 237.653 237.653 ↓ 0.0 0 237,653

Index Only Scan using th_days_to_show_release_as_spt_pkey on th_days_to_show_release_as_spt spt_days (cost=0.42..0.45 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=237,653)

  • Index Cond: ((release_no = x.release_no) AND (exhibition_date = x.exhibition_date))
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0 237,650

Materialize (cost=3,074.62..6,255.87 rows=1 width=28) (actual time=0.000..0.000 rows=0 loops=237,650)

29. 0.000 20.820 ↓ 0.0 0 1

Nested Loop (cost=3,074.62..6,255.86 rows=1 width=28) (actual time=20.820..20.820 rows=0 loops=1)

  • Join Filter: ((mtd.exhibition_date >= w_1.from_date) AND (mtd.exhibition_date <= w_1.to_date) AND (th_week(mtd.exhibition_date, w_1.day1, w_1.rollback_date) >= o_1.min_exhibition_week) AND (th_week(mtd.exhibition_date, w_1.day1, w_1.rollback_date) <= o_1.max_exhibition_week))
30. 0.001 20.820 ↓ 0.0 0 1

Nested Loop (cost=3,074.48..6,225.83 rows=44 width=47) (actual time=20.820..20.820 rows=0 loops=1)

  • Join Filter: (r_2.release_no = r_1.release_no)
31. 1.070 20.819 ↓ 0.0 0 1

Hash Join (cost=3,074.05..6,167.31 rows=44 width=48) (actual time=20.819..20.819 rows=0 loops=1)

  • Hash Cond: ((mtd.theater_no = o_1.theater_no) AND (r_2.release_no = o_1.release_no))
32. 5.931 13.833 ↑ 1.6 13,520 1

HashAggregate (cost=2,164.05..2,381.09 rows=21,704 width=28) (actual time=11.597..13.833 rows=13,520 loops=1)

  • Group Key: mtd.multi_feature_no, mtd.theater_no, mtd.exhibition_date, r_2.release_no
33. 1.641 7.902 ↑ 1.6 13,520 1

Hash Join (cost=1,522.01..1,892.75 rows=21,704 width=28) (actual time=3.936..7.902 rows=13,520 loops=1)

  • Hash Cond: (mtd.multi_feature_no = m_count.multi_feature_no)
34. 1.735 5.848 ↑ 1.0 13,520 1

Hash Join (cost=1,481.45..1,816.14 rows=13,555 width=24) (actual time=3.516..5.848 rows=13,520 loops=1)

  • Hash Cond: (mtd.multi_feature_no = mft.multi_feature_no)
35. 0.617 0.617 ↑ 1.0 6,743 1

Seq Scan on th_multi_features_by_th_day mtd (cost=0.00..106.43 rows=6,743 width=16) (actual time=0.011..0.617 rows=6,743 loops=1)

36. 0.153 3.496 ↑ 1.0 1,173 1

Hash (cost=1,466.78..1,466.78 rows=1,173 width=8) (actual time=3.496..3.496 rows=1,173 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 62kB
37. 0.000 3.343 ↑ 1.0 1,173 1

Nested Loop (cost=0.42..1,466.78 rows=1,173 width=8) (actual time=0.020..3.343 rows=1,173 loops=1)

38. 0.106 0.106 ↑ 1.0 1,173 1

Seq Scan on th_multi_feature_releases mft (cost=0.00..17.73 rows=1,173 width=8) (actual time=0.002..0.106 rows=1,173 loops=1)

39. 3.519 3.519 ↑ 1.0 1 1,173

Index Only Scan using ti_releases_pkey on ti_releases r_2 (cost=0.42..1.24 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1,173)

  • Index Cond: (release_no = mft.release_no)
  • Heap Fetches: 0
40. 0.052 0.413 ↑ 1.0 522 1

Hash (cost=34.04..34.04 rows=522 width=12) (actual time=0.412..0.413 rows=522 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 31kB
41. 0.030 0.361 ↑ 1.0 522 1

Subquery Scan on m_count (cost=23.60..34.04 rows=522 width=12) (actual time=0.281..0.361 rows=522 loops=1)

42. 0.234 0.331 ↑ 1.0 522 1

HashAggregate (cost=23.60..28.82 rows=522 width=12) (actual time=0.280..0.331 rows=522 loops=1)

  • Group Key: mfr.multi_feature_no
43. 0.097 0.097 ↑ 1.0 1,173 1

Seq Scan on th_multi_feature_releases mfr (cost=0.00..17.73 rows=1,173 width=4) (actual time=0.012..0.097 rows=1,173 loops=1)

44. 2.957 5.916 ↑ 1.0 25,000 1

Hash (cost=535.00..535.00 rows=25,000 width=24) (actual time=5.916..5.916 rows=25,000 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1624kB
45. 2.959 2.959 ↑ 1.0 25,000 1

Seq Scan on tmp_th_out_of_date_xtns_rebuilding_weurope o_1 (cost=0.00..535.00 rows=25,000 width=24) (actual time=0.006..2.959 rows=25,000 loops=1)

46. 0.000 0.000 ↓ 0.0 0

Index Scan using ti_releases_pkey on ti_releases r_1 (cost=0.42..1.32 rows=1 width=7) (never executed)

  • Index Cond: (release_no = o_1.release_no)
47. 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..0.16 rows=1 width=19) (never executed)

  • Index Cond: ((country_id)::text = (r_1.country_id)::text)
48. 0.001 405.978 ↓ 0.0 0 1

Hash (cost=71,063.42..71,063.42 rows=4,430 width=48) (actual time=405.978..405.978 rows=0 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 64kB
49. 0.001 405.977 ↓ 0.0 0 1

Subquery Scan on more_x (cost=70,875.15..71,063.42 rows=4,430 width=48) (actual time=405.977..405.977 rows=0 loops=1)

50. 0.001 405.976 ↓ 0.0 0 1

GroupAggregate (cost=70,875.15..71,019.12 rows=4,430 width=48) (actual time=405.976..405.976 rows=0 loops=1)

  • Group Key: x_1.theater_no, x_1.release_no, r_3.release_date
51. 0.006 405.975 ↓ 0.0 0 1

Sort (cost=70,875.15..70,886.22 rows=4,430 width=40) (actual time=405.975..405.975 rows=0 loops=1)

  • Sort Key: x_1.theater_no, x_1.release_no, r_3.release_date
  • Sort Method: quicksort Memory: 25kB
52. 0.001 405.969 ↓ 0.0 0 1

Nested Loop Left Join (cost=57,873.62..70,606.84 rows=4,430 width=40) (actual time=405.969..405.969 rows=0 loops=1)

53. 14.829 405.968 ↓ 0.0 0 1

Hash Join (cost=57,873.06..62,621.86 rows=4,430 width=42) (actual time=405.968..405.968 rows=0 loops=1)

  • Hash Cond: (x_1.release_no = r_3.release_no)
  • Join Filter: ((x_1.exhibition_date <= (r_3.release_date - '1 day'::interval)) AND (x_1.exhibition_date >= (r_3.release_date - (((r_3.num_prerelease_days_to_include)::text || ' day'::text))::interval)))
  • Rows Removed by Join Filter: 2
54. 24.018 24.018 ↑ 1.0 237,687 1

Seq Scan on tmp_ood_xtns_44244980 x_1 (cost=0.00..4,124.87 rows=237,687 width=34) (actual time=0.012..24.018 rows=237,687 loops=1)

55. 27.537 367.121 ↓ 1.0 134,474 1

Hash (cost=56,192.32..56,192.32 rows=134,459 width=16) (actual time=367.121..367.121 rows=134,474 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 8225kB
56. 339.584 339.584 ↓ 1.0 134,474 1

Seq Scan on ti_releases r_3 (cost=0.00..56,192.32 rows=134,459 width=16) (actual time=0.014..339.584 rows=134,474 loops=1)

  • Filter: (num_prerelease_days_to_include > '0'::numeric)
  • Rows Removed by Filter: 667327
57. 0.000 0.000 ↓ 0.0 0

Index Scan using unq_th_showtimes_info on th_showtimes_info s (cost=0.56..1.80 rows=1 width=14) (never executed)

  • Index Cond: ((theater_no = x_1.theater_no) AND (release_no = x_1.release_no) AND (exhibition_date = x_1.exhibition_date))
58. 0.000 320.173 ↓ 0.0 0 1

Hash (cost=61,066.64..61,066.64 rows=49 width=48) (actual time=320.173..320.173 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
59. 0.001 320.173 ↓ 0.0 0 1

Subquery Scan on pre_release_rev_to_include_in_cume_x (cost=61,064.56..61,066.64 rows=49 width=48) (actual time=320.173..320.173 rows=0 loops=1)

60. 0.002 320.172 ↓ 0.0 0 1

GroupAggregate (cost=61,064.56..61,066.15 rows=49 width=48) (actual time=320.172..320.172 rows=0 loops=1)

  • Group Key: x_2.theater_no, x_2.release_no, r_4.release_date
61. 0.007 320.170 ↓ 0.0 0 1

Sort (cost=61,064.56..61,064.68 rows=49 width=40) (actual time=320.170..320.170 rows=0 loops=1)

  • Sort Key: x_2.theater_no, x_2.release_no, r_4.release_date
  • Sort Method: quicksort Memory: 25kB
62. 0.000 320.163 ↓ 0.0 0 1

Nested Loop Left Join (cost=56,226.62..61,063.18 rows=49 width=40) (actual time=320.163..320.163 rows=0 loops=1)

63. 14.138 320.163 ↓ 0.0 0 1

Hash Join (cost=56,226.06..60,974.86 rows=49 width=42) (actual time=320.163..320.163 rows=0 loops=1)

  • Hash Cond: (x_2.release_no = r_4.release_no)
  • Join Filter: ((x_2.exhibition_date <= (r_4.release_date - '1 day'::interval)) AND (x_2.exhibition_date >= (r_4.release_date - (((r_4.num_prerelease_days_to_include_in_cume)::text || ' day'::text))::interval)) AND ((x_2.exhibition_date < (r_4.release_date - (((r_4.num_prerelease_days_to_include)::text || ' day'::text))::interval)) OR (x_2.exhibition_date > (r_4.release_date - '1 day'::interval))))
  • Rows Removed by Join Filter: 2
64. 15.612 15.612 ↑ 1.0 237,687 1

Seq Scan on tmp_ood_xtns_44244980 x_2 (cost=0.00..4,124.87 rows=237,687 width=34) (actual time=0.010..15.612 rows=237,687 loops=1)

65. 0.495 290.413 ↑ 1.2 2,273 1

Hash (cost=56,192.32..56,192.32 rows=2,699 width=20) (actual time=290.413..290.413 rows=2,273 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 149kB
66. 289.918 289.918 ↑ 1.2 2,273 1

Seq Scan on ti_releases r_4 (cost=0.00..56,192.32 rows=2,699 width=20) (actual time=3.400..289.918 rows=2,273 loops=1)

  • Filter: (num_prerelease_days_to_include_in_cume > 0)
  • Rows Removed by Filter: 799528
67. 0.000 0.000 ↓ 0.0 0

Index Scan using unq_th_showtimes_info on th_showtimes_info s_1 (cost=0.56..1.80 rows=1 width=14) (never executed)

  • Index Cond: ((theater_no = x_2.theater_no) AND (release_no = x_2.release_no) AND (exhibition_date = x_2.exhibition_date))
Planning time : 21.036 ms
Execution time : 12,269.704 ms