explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TXgu

Settings
# exclusive inclusive rows x rows loops node
1. 30.044 349.196 ↑ 1.0 4,561 1

Hash Join (cost=7,757.76..8,060.32 rows=4,567 width=1,111) (actual time=312.627..349.196 rows=4,561 loops=1)

  • Output: 'c', c.id, (NULL::unknown), (array_agg(d.state ORDER BY d.id)), (array_agg(d.id ORDER BY d.id)), (array_agg(COALESCE(d.dlr_in_time_dt, d.i_time_dt) ORDER BY d.id)), (array_agg((CASE WHEN (d.dlr_in_time_dt IS NULL) THEN 1 ELSE 0 END)::smallint ORDER BY d.id)), (array_agg(d.dlr_in_time_resp_dt ORDER BY d.id)), cdr_20200314.c_sms_id, cdr_20200314.c_route_num, cdr_20200314.c_attempt, cdr_20200314.c_is_last, cdr_20200314.c_status, cdr_20200314.c_code, cdr_20200314.i_code, cdr_20200314.o_code, cdr_20200314.i_ip, cdr_20200314.o_ip, cdr_20200314.i_port, cdr_20200314.o_port, cdr_20200314.c_src_rtg_num, cdr_20200314.c_dst_rtg_num, cdr_20200314.c_lrn, cdr_20200314.c_rtg_num, cdr_20200314.c_dst_mccmnc, cdr_20200314.i_src_num, cdr_20200314.o_src_num, cdr_20200314.i_dst_num, cdr_20200314.o_dst_num, cdr_20200314.i_src_num_ton, cdr_20200314.o_src_num_ton, cdr_20200314.i_dst_num_ton, cdr_20200314.o_dst_num_ton, cdr_20200314.i_src_num_npi, cdr_20200314.o_src_num_npi, cdr_20200314.i_dst_num_npi, cdr_20200314.o_dst_num_npi, cdr_20200314.i_username, cdr_20200314.o_username, cdr_20200314.i_time, cdr_20200314.i_time_resp, cdr_20200314.o_time, cdr_20200314.o_time_resp, cdr_20200314.i_cust_id, cdr_20200314.o_vend_id, cdr_20200314.i_gw_id, cdr_20200314.o_gw_id, cdr_20200314.i_rate_plan_id, cdr_20200314.i_deck_id, cdr_20200314.i_area_id, cdr_20200314.o_rate_plan_id, cdr_20200314.i_rate_id, cdr_20200314.o_rate_id, cdr_20200314.i_route_plan_id, cdr_20200314.o_route_plan_id, cdr_20200314.i_rate_sys, cdr_20200314.o_rate_sys, cdr_20200314.i_currency, cdr_20200314.o_currency, cdr_20200314.i_rate, cdr_20200314.o_rate, cdr_20200314.o_dp_id, cdr_20200314.i_account_id, cdr_20200314.o_account_id, cdr_20200314.c_src_num, cdr_20200314.c_dst_num, cdr_20200314.c_src_lrn, cdr_20200314.c_dst_lrn, cdr_20200314.c_src_state, cdr_20200314.c_dst_state, cdr_20200314.c_src_lata, cdr_20200314.c_dst_lata, cdr_20200314.c_src_ocn, cdr_20200314.c_dst_ocn, cdr_20200314.i_seq, cdr_20200314.o_seq, cdr_20200314.i_ip_local, cdr_20200314.o_ip_local, cdr_20200314.i_port_local, cdr_20200314.o_port_local, cdr_20200314.i_message_id_c, cdr_20200314.o_message_id_c, cdr_20200314.c_sm_body, cdr_20200314.i_seq_ref_num, cdr_20200314.i_seq_total_segments, cdr_20200314.i_seq_segment_num, cdr_20200314.i_seq_id, cdr_20200314.c_tags, cdr_20200314.o_trunk_id, cdr_20200314.i_trunk_id, cdr_20200314.o_sm_body, cdr_20200314.o_route_id, cdr_20200314.o_prefix, cdr_20200314.o_tags, cdr_20200314.o_area_name, cdr_20200314.c_src_num_ton, cdr_20200314.c_src_num_npi, cdr_20200314.c_dst_num_ton, cdr_20200314.c_dst_num_npi, cdr_20200314.c_src_mccmnc, cdr_20200314.i_prefix, cdr_20200314.c_jurisdiction
  • Hash Cond: (c.id = cdr_20200314.id)
  • Buffers: shared hit=7152
2. 29.466 52.386 ↑ 1.0 4,561 1

GroupAggregate (cost=1,158.28..1,352.38 rows=4,567 width=42) (actual time=22.439..52.386 rows=4,561 loops=1)

  • Output: c.id, NULL::unknown, array_agg(d.state ORDER BY d.id), array_agg(d.id ORDER BY d.id), array_agg(COALESCE(d.dlr_in_time_dt, d.i_time_dt) ORDER BY d.id), array_agg((CASE WHEN (d.dlr_in_time_dt IS NULL) THEN 1 ELSE 0 END)::smallint ORDER BY d.id), array_agg(d.dlr_in_time_resp_dt ORDER BY d.id)
  • Group Key: c.id
  • Buffers: shared hit=2001
3. 3.096 22.920 ↓ 1.0 4,585 1

Sort (cost=1,158.28..1,169.70 rows=4,567 width=42) (actual time=22.373..22.920 rows=4,585 loops=1)

  • Output: c.id, d.state, d.id, d.dlr_in_time_dt, d.i_time_dt, d.dlr_in_time_resp_dt
  • Sort Key: c.id
  • Sort Method: quicksort Memory: 525kB
  • Buffers: shared hit=2001
4. 3.078 19.824 ↓ 1.0 4,585 1

Hash Left Join (cost=264.39..880.67 rows=4,567 width=42) (actual time=9.883..19.824 rows=4,585 loops=1)

  • Output: c.id, d.state, d.id, d.dlr_in_time_dt, d.i_time_dt, d.dlr_in_time_resp_dt
  • Hash Cond: (c.c_sms_id = d.sms)
  • Join Filter: (c.c_status = 3)
  • Buffers: shared hit=2001
5. 6.951 6.951 ↑ 1.0 4,561 1

Index Scan using cdr_20200314_pkey on smscdr.cdr_20200314 c (cost=0.29..583.22 rows=4,567 width=20) (actual time=0.065..6.951 rows=4,561 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
  • Index Cond: ((c.id > 16610283) AND (c.id <= 16620283))
  • Filter: ((c.i_time >= '2020-03-14 00:00:00'::timestamp without time zone) AND (c.i_time < '2020-03-15 00:00:00'::timestamp without time zone))
  • Buffers: shared hit=1201
6. 2.962 9.795 ↓ 1.0 3,754 1

Hash (cost=217.51..217.51 rows=3,727 width=42) (actual time=9.795..9.795 rows=3,754 loops=1)

  • Output: d.state, d.id, d.dlr_in_time_dt, d.i_time_dt, d.dlr_in_time_resp_dt, d.sms
  • Buckets: 4096 Batches: 1 Memory Usage: 326kB
  • Buffers: shared hit=800
7. 6.833 6.833 ↓ 1.0 3,754 1

Index Scan using dlr_20200314_pkey on smscdr.dlr_20200314 d (cost=0.29..217.51 rows=3,727 width=42) (actual time=0.053..6.833 rows=3,754 loops=1)

  • Output: d.state, d.id, d.dlr_in_time_dt, d.i_time_dt, d.dlr_in_time_resp_dt, d.sms
  • Index Cond: ((d.id > 16610283) AND (d.id <= 16620283))
  • Filter: ((d.sms_in_time >= 1584144000) AND (d.sms_in_time < 1584230400))
  • Buffers: shared hit=800
8. 156.260 266.766 ↓ 1.0 64,500 1

Hash (cost=5,794.77..5,794.77 rows=64,377 width=919) (actual time=266.766..266.766 rows=64,500 loops=1)

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

Seq Scan on smscdr.cdr_20200314 (cost=0.00..5,794.77 rows=64,377 width=919) (actual time=0.017..110.506 rows=64,500 loops=1)

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