explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vuhB : Making changes to support

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

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

2. 0.155 3,090.406 ↓ 50.8 254 1

Group (cost=1,260,581.29..1,260,581.33 rows=5 width=52) (actual time=3,090.200..3,090.406 rows=254 loops=1)

  • Group Key: b.rpt_orgn_lnp_key, b.program_selection
3. 0.203 3,090.251 ↓ 50.8 254 1

Sort (cost=1,260,581.29..1,260,581.30 rows=5 width=52) (actual time=3,090.199..3,090.251 rows=254 loops=1)

  • Sort Key: b.rpt_orgn_lnp_key, b.program_selection
  • Sort Method: quicksort Memory: 44kB
4. 0.105 3,090.048 ↓ 50.8 254 1

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

5. 0.540 3,089.943 ↓ 50.8 254 1

HashAggregate (cost=1,260,581.13..1,260,581.18 rows=5 width=190) (actual time=3,089.834..3,089.943 rows=254 loops=1)

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

Append (cost=0.00..1,260,581.07 rows=5 width=190) (actual time=174.113..3,089.403 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.001..0.001 rows=0 loops=1)

  • One-Time Filter: false
8. 8.700 1,546.338 ↓ 84.7 254 1

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

  • Workers Planned: 2
  • Workers Launched: 2
9. 0.312 1,537.638 ↓ 85.0 85 3 / 3

Hash Join (cost=33,532.24..571,184.21 rows=1 width=67) (actual time=167.700..1,537.638 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. 1,487.016 1,534.555 ↓ 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=164.866..1,534.555 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))
  • Rows Removed by Index Recheck: 382522
  • Filter: (((rpt_daypart_code)::text = 'EF'::text) AND (ntv_rpt_prgm_id = '222'::numeric))
  • Rows Removed by Filter: 541948
  • Heap Blocks: exact=21333 lossy=56364
11. 47.539 47.539 ↓ 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=142.615..142.616 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.305 2.771 ↑ 1.0 2,278 3 / 3

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

  • Buckets: 4096 Batches: 1 Memory Usage: 139kB
13. 1.466 1.466 ↑ 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.010..1.466 rows=2,278 loops=3)

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

Nested Loop (cost=34,429.29..688,396.52 rows=1 width=67) (actual time=1,546.023..1,546.023 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. 7.169 1,548.723 ↓ 0.0 0 1

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

  • Workers Planned: 2
  • Workers Launched: 2
16. 1,495.930 1,541.554 ↓ 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=1,541.554..1,541.554 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))
  • Rows Removed by Index Recheck: 382522
  • 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=19799 lossy=53478
17. 45.624 45.624 ↓ 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=136.873..136.873 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))