explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lkT3 : Making changes to support

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 1,495.244 ↑ 1.0 1 1

Aggregate (cost=1,260,581.39..1,260,581.40 rows=1 width=8) (actual time=1,495.244..1,495.244 rows=1 loops=1)

2. 0.154 1,495.186 ↓ 50.8 254 1

Group (cost=1,260,581.29..1,260,581.33 rows=5 width=52) (actual time=1,494.982..1,495.186 rows=254 loops=1)

  • Group Key: b.rpt_orgn_lnp_key, b.program_selection
3. 0.180 1,495.032 ↓ 50.8 254 1

Sort (cost=1,260,581.29..1,260,581.30 rows=5 width=52) (actual time=1,494.980..1,495.032 rows=254 loops=1)

  • Sort Key: b.rpt_orgn_lnp_key, b.program_selection
  • Sort Method: quicksort Memory: 44kB
4. 0.112 1,494.852 ↓ 50.8 254 1

Subquery Scan on b (cost=1,260,581.13..1,260,581.23 rows=5 width=52) (actual time=1,494.639..1,494.852 rows=254 loops=1)

5. 0.417 1,494.740 ↓ 50.8 254 1

HashAggregate (cost=1,260,581.13..1,260,581.18 rows=5 width=190) (actual time=1,494.638..1,494.740 rows=254 loops=1)

  • Group Key: telecast_name, orgn_dstr_id, trackage_id, rpt_orgn_lnp_key, ('Program Selection 1'::text)
6. 0.000 1,494.323 ↓ 50.8 254 1

Append (cost=0.00..1,260,581.07 rows=5 width=190) (actual time=229.017..1,494.323 rows=254 loops=1)

7. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..0.00 rows=0 width=67) (actual time=0.000..0.001 rows=0 loops=1)

  • One-Time Filter: false
8. 10.613 668.155 ↓ 84.7 254 1

Gather (cost=34,532.24..572,184.51 rows=3 width=67) (actual time=229.015..668.155 rows=254 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 0.278 657.542 ↓ 85.0 85 3 / 3

Hash Join (cost=33,532.24..571,184.21 rows=1 width=67) (actual time=224.994..657.542 rows=85 loops=3)

  • Hash Cond: (l.orgn_dstr_id = org.orgn_dstr_id)
  • Join Filter: ((l.ntv_bcast_date >= org.effective_start_date) AND (l.ntv_bcast_date <= org.effective_end_date))
  • Rows Removed by Join Filter: 85
10. 601.046 654.560 ↓ 28.3 85 3 / 3

Parallel Bitmap Heap Scan on rpt_orgn_lnp_backupmay19 l (cost=33,429.29..571,080.86 rows=3 width=44) (actual time=222.232..654.560 rows=85 loops=3)

  • Recheck Cond: ((ntv_bcast_date >= to_date('10/01/2017'::text, 'mm/dd/yyyy'::text)) AND (ntv_bcast_date <= to_date('10/10/2018'::text, 'mm/dd/yyyy'::text)) AND (released_for_processing_flg = 'Y'::bpchar) AND (lnp_release_type_code = ANY ('{1,3}'::numeric[])) AND (complex_id = '0'::numeric))
  • Filter: (((rpt_daypart_code)::text = 'EF'::text) AND (ntv_rpt_prgm_id = '222'::numeric))
  • Rows Removed by Filter: 541948
  • Heap Blocks: exact=77435
11. 53.514 53.514 ↓ 1.0 1,626,099 1 / 3

Bitmap Index Scan on idx_rol_ntv_date (cost=0.00..33,429.29 rows=1,594,085 width=0) (actual time=160.541..160.541 rows=1,626,099 loops=1)

  • Index Cond: ((ntv_bcast_date >= to_date('10/01/2017'::text, 'mm/dd/yyyy'::text)) AND (ntv_bcast_date <= to_date('10/10/2018'::text, 'mm/dd/yyyy'::text)))
12. 1.280 2.704 ↑ 1.0 2,278 3 / 3

Hash (cost=74.47..74.47 rows=2,278 width=13) (actual time=2.704..2.704 rows=2,278 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 139kB
13. 1.424 1.424 ↑ 1.0 2,278 3 / 3

Seq Scan on national_tv_content_originator org (cost=0.00..74.47 rows=2,278 width=13) (actual time=0.009..1.424 rows=2,278 loops=3)

  • Filter: (released_for_processing_flag = 'Y'::bpchar)
14. 0.000 831.762 ↓ 0.0 0 1

Nested Loop (cost=34,429.29..688,396.52 rows=1 width=67) (actual time=831.761..831.762 rows=0 loops=1)

  • Join Filter: ((l_1.ntv_bcast_date >= org_1.effective_start_date) AND (l_1.ntv_bcast_date <= org_1.effective_end_date))
15. 10.997 837.024 ↓ 0.0 0 1

Gather (cost=34,429.29..688,316.32 rows=1 width=44) (actual time=831.760..837.024 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 772.203 826.027 ↓ 0.0 0 3 / 3

Parallel Bitmap Heap Scan on rpt_orgn_lnp_backupmay19 l_1 (cost=33,429.29..687,316.22 rows=1 width=44) (actual time=826.026..826.027 rows=0 loops=3)

  • Recheck Cond: ((ntv_bcast_date >= to_date('10/01/2017'::text, 'mm/dd/yyyy'::text)) AND (ntv_bcast_date <= to_date('10/10/2018'::text, 'mm/dd/yyyy'::text)) AND (released_for_processing_flg = 'Y'::bpchar) AND (lnp_release_type_code = ANY ('{1,3}'::numeric[])) AND (complex_id = '0'::numeric))
  • Filter: ((orgn_dstr_id = '77'::numeric) AND (trackage_id = '0'::numeric) AND (orgn_type_code = '8'::numeric) AND (ntv_rpt_prgm_id = '135'::numeric) AND ((rpt_daypart_code)::text = 'LF'::text) AND ((epsd_name)::text = 'N/A'::text) AND (CASE WHEN (((cmrc_sts_code)::text = ANY ('{R,S}'::text[])) AND (orgn_type_code = '8'::numeric)) THEN 'Y'::text ELSE 'N'::text END = 'N'::text) AND (CASE WHEN (((orgn_type_code = '8'::numeric) OR (orgn_type_code = '12'::numeric)) AND (is_premier_flg = 'Y'::bpchar)) THEN 'Y'::text WHEN ((orgn_type_code = '9'::numeric) AND (proprietary_is_premiere_flg = 'Y'::bpchar)) THEN 'Y'::text ELSE 'N'::text END = 'N'::text) AND (CASE WHEN ((orgn_type_code = '9'::numeric) AND (proprietary_is_repeat_flg = 'Y'::bpchar)) THEN 'Y'::text WHEN (((orgn_type_code = '8'::numeric) OR (orgn_type_code = '12'::numeric)) AND (is_repeat_flg = 'Y'::bpchar)) THEN 'Y'::text ELSE 'N'::text END = 'N'::text) AND (CASE WHEN ((((orgn_type_code = '8'::numeric) OR (orgn_type_code = '12'::numeric)) AND ((is_special_flg = 'Y'::bpchar) OR (breakout_type_code = '3'::numeric))) OR (breakout_type_code = '0'::numeric)) THEN 'N'::text WHEN (((is_breakout_flg = 'Y'::bpchar) OR (is_breakout_flg = '6'::bpchar) OR (is_breakout_flg = '7'::bpchar) OR (is_breakout_flg = '8'::bpchar) OR (is_breakout_flg = '9'::bpchar)) AND (breakout_type_code <> '3'::numeric)) THEN 'Y'::text ELSE 'N'::text END = 'N'::text) AND (btrim(((((((((((((((((((((COALESCE(to_char(orgn_type_code, '99999'::text), '-'::text) || (COALESCE(is_special_flg, '-'::bpchar))::text) || (COALESCE(proprietary_is_repeat_flg, '-'::bpchar))::text) || (COALESCE(is_repeat_flg, '-'::bpchar))::text) || (COALESCE(is_live_bcast_flg, '-'::bpchar))::text) || (COALESCE(is_breakout_flg, '-'::bpchar))::text) || COALESCE(to_char(breakout_type_code, '99999'::text), '-'::text)) || (COALESCE(is_gapped_flg, '-'::bpchar))::text) || (COALESCE(is_premier_flg, '-'::bpchar))::text) || (COALESCE(proprietary_is_premiere_flg, '-'::bpchar))::text) || (COALESCE(has_shrt_duration_flg, '-'::bpchar))::text) || (COALESCE(prgm_is_movie_flg, '-'::bpchar))::text) || (COALESCE(prgm_orig_aquired_code, '-'::character varying))::text) || (COALESCE(is_complex_prgm_flg, '-'::bpchar))::text) || (COALESCE(epsd_orig_aquired_code, '-'::character varying))::text) || (COALESCE(prgm_is_sports_event_flg, '-'::bpchar))::text) || (COALESCE(has_cmrc_cntn_flg, '-'::bpchar))::text) || (COALESCE(cmrc_sts_code, '-'::character varying))::text) || (COALESCE(is_umbrella_flg, '-'::bpchar))::text) || COALESCE(to_char(nmbr_of_rpt_weeks, '99999'::text), '-'::text)) || (COALESCE(cmrc_sts_code, '-'::character varying))::text)) = '8NNNNN0NYNNN-N-NYCN1C'::text))
  • Rows Removed by Filter: 542033
  • Heap Blocks: exact=79582
17. 53.824 53.824 ↓ 1.0 1,626,099 1 / 3

Bitmap Index Scan on idx_rol_ntv_date (cost=0.00..33,429.29 rows=1,594,085 width=0) (actual time=161.472..161.472 rows=1,626,099 loops=1)

  • Index Cond: ((ntv_bcast_date >= to_date('10/01/2017'::text, 'mm/dd/yyyy'::text)) AND (ntv_bcast_date <= to_date('10/10/2018'::text, 'mm/dd/yyyy'::text)))
18. 0.000 0.000 ↓ 0.0 0

Seq Scan on national_tv_content_originator org_1 (cost=0.00..80.17 rows=2 width=13) (never executed)

  • Filter: ((orgn_dstr_id = '77'::numeric) AND (released_for_processing_flag = 'Y'::bpchar))