explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eag0

Settings
# exclusive inclusive rows x rows loops node
1. 2.324 59.486 ↓ 11.2 528 1

Hash Right Join (cost=871.04..968.82 rows=47 width=1,501) (actual time=7.605..59.486 rows=528 loops=1)

  • Output: c.id, c.c_sms_id, c.c_route_num, c.c_attempt, c.c_is_last, c.c_status, c.c_code, c.i_code, c.o_code, c.i_ip, c.o_ip, c.i_port, c.o_port, c.c_src_rtg_num, c.c_dst_rtg_num, c.c_lrn, c.c_rtg_num, c.c_dst_mccmnc, c.i_src_num, c.o_src_num, c.i_dst_num, c.o_dst_num, c.i_src_num_ton, c.o_src_num_ton, c.i_dst_num_ton, c.o_dst_num_ton, c.i_src_num_npi, c.o_src_num_npi, c.i_dst_num_npi, c.o_dst_num_npi, c.i_username, c.o_username, c.i_time, c.i_time_resp, c.o_time, c.o_time_resp, c.i_cust_id, c.o_vend_id, c.i_gw_id, c.o_gw_id, c.i_rate_plan_id, c.i_deck_id, c.i_area_id, c.o_rate_plan_id, c.i_rate_id, c.o_rate_id, c.i_route_plan_id, c.o_route_plan_id, c.i_rate_sys, c.o_rate_sys, c.i_currency, c.o_currency, c.i_rate, c.o_rate, c.o_dp_id, c.i_account_id, c.o_account_id, c.c_src_num, c.c_dst_num, c.c_src_lrn, c.c_dst_lrn, c.c_src_state, c.c_dst_state, c.c_src_lata, c.c_dst_lata, c.c_src_ocn, c.c_dst_ocn, c.i_seq, c.o_seq, c.i_ip_local, c.o_ip_local, c.i_port_local, c.o_port_local, c.i_message_id_c, c.o_message_id_c, c.c_sm_body, c.i_seq_ref_num, c.i_seq_total_segments, c.i_seq_segment_num, c.i_seq_id, c.c_tags, c.o_trunk_id, c.i_trunk_id, c.o_sm_body, c.o_route_id, c.o_prefix, c.o_tags, c.o_area_name, c.c_src_num_ton, c.c_src_num_npi, c.c_dst_num_ton, c.c_dst_num_npi, c.c_src_mccmnc, c.i_prefix, c.c_jurisdiction, 'c', NULL::unknown, d.dlr_st, d.dlr_id, c.i_message_id_c, c.o_message_id_c, d.dlr_times, d.dlr_times_resp, d.dlr_status
  • Hash Cond: (d.sms = c.c_sms_id)
  • Join Filter: (c.c_status = 3)
  • Buffers: shared hit=885
2.          

CTE cdr

3. 0.355 1.383 ↓ 11.2 528 1

Bitmap Heap Scan on smscdr.cdr_20200314 c_1 (cost=117.10..292.57 rows=47 width=919) (actual time=1.049..1.383 rows=528 loops=1)

  • Output: c_1.id, c_1.c_sms_id, c_1.c_route_num, c_1.c_attempt, c_1.c_is_last, c_1.c_status, c_1.c_code, c_1.i_code, c_1.o_code, c_1.i_ip, c_1.o_ip, c_1.i_port, c_1.o_port, c_1.c_src_rtg_num, c_1.c_dst_rtg_num, c_1.c_lrn, c_1.c_rtg_num, c_1.c_dst_mccmnc, c_1.i_src_num, c_1.o_src_num, c_1.i_dst_num, c_1.o_dst_num, c_1.i_src_num_ton, c_1.o_src_num_ton, c_1.i_dst_num_ton, c_1.o_dst_num_ton, c_1.i_src_num_npi, c_1.o_src_num_npi, c_1.i_dst_num_npi, c_1.o_dst_num_npi, c_1.i_username, c_1.o_username, c_1.i_time, c_1.i_time_resp, c_1.o_time, c_1.o_time_resp, c_1.i_cust_id, c_1.o_vend_id, c_1.i_gw_id, c_1.o_gw_id, c_1.i_rate_plan_id, c_1.i_deck_id, c_1.i_area_id, c_1.o_rate_plan_id, c_1.i_rate_id, c_1.o_rate_id, c_1.i_route_plan_id, c_1.o_route_plan_id, c_1.i_rate_sys, c_1.o_rate_sys, c_1.i_currency, c_1.o_currency, c_1.i_rate, c_1.o_rate, c_1.o_dp_id, c_1.i_account_id, c_1.o_account_id, c_1.c_src_num, c_1.c_dst_num, c_1.c_src_lrn, c_1.c_dst_lrn, c_1.c_src_state, c_1.c_dst_state, c_1.c_src_lata, c_1.c_dst_lata, c_1.c_src_ocn, c_1.c_dst_ocn, c_1.i_seq, c_1.o_seq, c_1.i_ip_local, c_1.o_ip_local, c_1.i_port_local, c_1.o_port_local, c_1.i_message_id_c, c_1.o_message_id_c, c_1.c_sm_body, c_1.i_seq_ref_num, c_1.i_seq_total_segments, c_1.i_seq_segment_num, c_1.i_seq_id, c_1.c_tags, c_1.o_trunk_id, c_1.i_trunk_id, c_1.o_sm_body, c_1.o_route_id, c_1.o_prefix, c_1.o_tags, c_1.o_area_name, c_1.c_src_num_ton, c_1.c_src_num_npi, c_1.c_dst_num_ton, c_1.c_dst_num_npi, c_1.c_src_mccmnc, c_1.i_prefix, c_1.c_jurisdiction
  • Recheck Cond: ((c_1.i_time >= '2020-03-14 00:00:00'::timestamp without time zone) AND (c_1.i_time < '2020-03-14 01:00:00'::timestamp without time zone) AND (c_1.id > 16610283) AND (c_1.id <= 16620283))
  • Heap Blocks: exact=66
  • Buffers: shared hit=85
4. 0.021 1.028 ↓ 0.0 0 1

BitmapAnd (cost=117.10..117.10 rows=47 width=0) (actual time=1.028..1.028 rows=0 loops=1)

  • Buffers: shared hit=19
5. 0.140 0.140 ↑ 1.0 641 1

Bitmap Index Scan on smscdr_cdr_20200314_i_time (cost=0.00..18.87 rows=658 width=0) (actual time=0.140..0.140 rows=641 loops=1)

  • Index Cond: ((c_1.i_time >= '2020-03-14 00:00:00'::timestamp without time zone) AND (c_1.i_time < '2020-03-14 01:00:00'::timestamp without time zone))
  • Buffers: shared hit=5
6. 0.867 0.867 ↑ 1.0 4,561 1

Bitmap Index Scan on cdr_20200314_pkey (cost=0.00..97.96 rows=4,567 width=0) (actual time=0.867..0.867 rows=4,561 loops=1)

  • Index Cond: ((c_1.id > 16610283) AND (c_1.id <= 16620283))
  • Buffers: shared hit=14
7.          

CTE dlr

8. 45.785 52.159 ↓ 1.0 3,730 1

GroupAggregate (cost=419.96..576.94 rows=3,664 width=42) (actual time=5.546..52.159 rows=3,730 loops=1)

  • Output: d_1.sms, array_agg(d_1.state ORDER BY d_1.id), array_agg(d_1.id ORDER BY d_1.id), array_agg(COALESCE(d_1.dlr_in_time_dt, d_1.i_time_dt) ORDER BY d_1.id), array_agg((CASE WHEN (d_1.dlr_in_time_dt IS NULL) THEN 1 ELSE 0 END)::smallint ORDER BY d_1.id), array_agg(d_1.dlr_in_time_resp_dt ORDER BY d_1.id)
  • Group Key: d_1.sms
  • Buffers: shared hit=800
9. 2.985 6.374 ↓ 1.0 3,754 1

Sort (cost=419.96..429.27 rows=3,727 width=42) (actual time=5.504..6.374 rows=3,754 loops=1)

  • Output: d_1.sms, d_1.state, d_1.id, d_1.dlr_in_time_dt, d_1.i_time_dt, d_1.dlr_in_time_resp_dt
  • Sort Key: d_1.sms
  • Sort Method: quicksort Memory: 390kB
  • Buffers: shared hit=800
10. 3.389 3.389 ↓ 1.0 3,754 1

Index Scan using dlr_20200314_pkey on smscdr.dlr_20200314 d_1 (cost=0.29..198.88 rows=3,727 width=42) (actual time=0.034..3.389 rows=3,754 loops=1)

  • Output: d_1.sms, d_1.state, d_1.id, d_1.dlr_in_time_dt, d_1.i_time_dt, d_1.dlr_in_time_resp_dt
  • Index Cond: ((d_1.id > 16610283) AND (d_1.id <= 16620283))
  • Buffers: shared hit=800
11. 55.145 55.145 ↓ 1.0 3,730 1

CTE Scan on dlr d (cost=0.00..73.28 rows=3,664 width=168) (actual time=5.549..55.145 rows=3,730 loops=1)

  • Output: d.sms, d.dlr_st, d.dlr_id, d.dlr_times, d.dlr_status, d.dlr_times_resp
  • Buffers: shared hit=800
12. 0.266 2.017 ↓ 11.2 528 1

Hash (cost=0.94..0.94 rows=47 width=1,341) (actual time=2.017..2.017 rows=528 loops=1)

  • Output: c.id, c.c_sms_id, c.c_route_num, c.c_attempt, c.c_is_last, c.c_status, c.c_code, c.i_code, c.o_code, c.i_ip, c.o_ip, c.i_port, c.o_port, c.c_src_rtg_num, c.c_dst_rtg_num, c.c_lrn, c.c_rtg_num, c.c_dst_mccmnc, c.i_src_num, c.o_src_num, c.i_dst_num, c.o_dst_num, c.i_src_num_ton, c.o_src_num_ton, c.i_dst_num_ton, c.o_dst_num_ton, c.i_src_num_npi, c.o_src_num_npi, c.i_dst_num_npi, c.o_dst_num_npi, c.i_username, c.o_username, c.i_time, c.i_time_resp, c.o_time, c.o_time_resp, c.i_cust_id, c.o_vend_id, c.i_gw_id, c.o_gw_id, c.i_rate_plan_id, c.i_deck_id, c.i_area_id, c.o_rate_plan_id, c.i_rate_id, c.o_rate_id, c.i_route_plan_id, c.o_route_plan_id, c.i_rate_sys, c.o_rate_sys, c.i_currency, c.o_currency, c.i_rate, c.o_rate, c.o_dp_id, c.i_account_id, c.o_account_id, c.c_src_num, c.c_dst_num, c.c_src_lrn, c.c_dst_lrn, c.c_src_state, c.c_dst_state, c.c_src_lata, c.c_dst_lata, c.c_src_ocn, c.c_dst_ocn, c.i_seq, c.o_seq, c.i_ip_local, c.o_ip_local, c.i_port_local, c.o_port_local, c.i_message_id_c, c.o_message_id_c, c.c_sm_body, c.i_seq_ref_num, c.i_seq_total_segments, c.i_seq_segment_num, c.i_seq_id, c.c_tags, c.o_trunk_id, c.i_trunk_id, c.o_sm_body, c.o_route_id, c.o_prefix, c.o_tags, c.o_area_name, c.c_src_num_ton, c.c_src_num_npi, c.c_dst_num_ton, c.c_dst_num_npi, c.c_src_mccmnc, c.i_prefix, c.c_jurisdiction
  • Buckets: 1024 Batches: 1 Memory Usage: 341kB
  • Buffers: shared hit=85
13. 1.751 1.751 ↓ 11.2 528 1

CTE Scan on cdr c (cost=0.00..0.94 rows=47 width=1,341) (actual time=1.053..1.751 rows=528 loops=1)

  • Output: c.id, c.c_sms_id, c.c_route_num, c.c_attempt, c.c_is_last, c.c_status, c.c_code, c.i_code, c.o_code, c.i_ip, c.o_ip, c.i_port, c.o_port, c.c_src_rtg_num, c.c_dst_rtg_num, c.c_lrn, c.c_rtg_num, c.c_dst_mccmnc, c.i_src_num, c.o_src_num, c.i_dst_num, c.o_dst_num, c.i_src_num_ton, c.o_src_num_ton, c.i_dst_num_ton, c.o_dst_num_ton, c.i_src_num_npi, c.o_src_num_npi, c.i_dst_num_npi, c.o_dst_num_npi, c.i_username, c.o_username, c.i_time, c.i_time_resp, c.o_time, c.o_time_resp, c.i_cust_id, c.o_vend_id, c.i_gw_id, c.o_gw_id, c.i_rate_plan_id, c.i_deck_id, c.i_area_id, c.o_rate_plan_id, c.i_rate_id, c.o_rate_id, c.i_route_plan_id, c.o_route_plan_id, c.i_rate_sys, c.o_rate_sys, c.i_currency, c.o_currency, c.i_rate, c.o_rate, c.o_dp_id, c.i_account_id, c.o_account_id, c.c_src_num, c.c_dst_num, c.c_src_lrn, c.c_dst_lrn, c.c_src_state, c.c_dst_state, c.c_src_lata, c.c_dst_lata, c.c_src_ocn, c.c_dst_ocn, c.i_seq, c.o_seq, c.i_ip_local, c.o_ip_local, c.i_port_local, c.o_port_local, c.i_message_id_c, c.o_message_id_c, c.c_sm_body, c.i_seq_ref_num, c.i_seq_total_segments, c.i_seq_segment_num, c.i_seq_id, c.c_tags, c.o_trunk_id, c.i_trunk_id, c.o_sm_body, c.o_route_id, c.o_prefix, c.o_tags, c.o_area_name, c.c_src_num_ton, c.c_src_num_npi, c.c_dst_num_ton, c.c_dst_num_npi, c.c_src_mccmnc, c.i_prefix, c.c_jurisdiction
  • Buffers: shared hit=85
Planning time : 1.013 ms
Execution time : 60.277 ms