explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CV1b

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 62,976,233.363 ↓ 0.0 0 1

Insert on bitelco.f_campaign_transition_characteristics (cost=79,504.50..79,504.74 rows=1 width=668) (actual time=62,976,233.363..62,976,233.363 rows=0 loops=1)

  • Buffers: shared hit=3210731186 read=547166005 dirtied=101
2. 0.001 62,976,233.361 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=79,504.50..79,504.74 rows=1 width=668) (actual time=62,976,233.361..62,976,233.361 rows=0 loops=1)

  • Output: "*SELECT*".time_partition_key, "*SELECT*".time_incentivized_key, "*SELECT*".hour_incentivized, "*SELECT*".time_positive_key, "*SELECT*".hour_positive, "*SELECT*".campaign_key, "*SELECT*".partition_field, "*SELECT*".previous_state_incentivized_key, "*SELECT*".previous_state_positive_key, "*SELECT*".state_incentivized_key, "*SELECT*".state_positive_key, "*SELECT*".event_incentivized_key, "*SELECT*".event_positive_key, "*SELECT*".transitions_characteristics_a_incentivized_key, "*SELECT*".transitions_characteristics_a_positive_key, "*SELECT*".transitions_characteristics_b_incentivized_key, "*SELECT*".transitions_characteristics_b_positive_key, "*SELECT*".target_audience_transitions, "*SELECT*".control_group_transitions, "*SELECT*".target_audience_entries, "*SELECT*".control_group_entries, "*SELECT*".target_audience_incentivized, "*SELECT*".control_group_incentivized, "*SELECT*".target_audience_positive, "*SELECT*".control_group_positive, "*SELECT*".target_audience_negative, "*SELECT*".control_group_negative, "*SELECT*".target_audience_exits, "*SELECT*".control_group_exits, "*SELECT*".target_audience_revenue, "*SELECT*".control_group_revenue, "*SELECT*".target_audience_cost, "*SELECT*".control_group_cost, "*SELECT*".customer_id, "*SELECT*".contract_id
  • Buffers: shared hit=3210731186 read=547166005 dirtied=101
3. 0.002 62,976,233.360 ↓ 0.0 0 1

GroupAggregate (cost=79,504.50..79,504.69 rows=1 width=412) (actual time=62,976,233.360..62,976,233.360 rows=0 loops=1)

  • Output: min((to_char(part_f_campaign_transition_detail.audit_time, 'YYYYMMdd'::text))::numeric), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN part_f_campaign_transition_detail.time_key ELSE 0 END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN (part_f_campaign_transition_detail.hour)::integer ELSE 0 END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN part_f_campaign_transition_detail.time_key ELSE 0 END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN (part_f_campaign_transition_detail.hour)::integer ELSE 0 END), part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN part_f_campaign_transition_detail.previous_state_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN part_f_campaign_transition_detail.previous_state_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN part_f_campaign_transition_detail.state_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN part_f_campaign_transition_detail.state_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN part_f_campaign_transition_detail.event_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN part_f_campaign_transition_detail.event_key ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN (split_part((part_f_campaign_transition_detail.transitions_characteristics_a)::text, '~@~'::text, 1))::bigint ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN (split_part((part_f_campaign_transition_detail.transitions_characteristics_a)::text, '~@~'::text, 1))::bigint ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 1) THEN (split_part((part_f_campaign_transition_detail.transitions_characteristics_b)::text, '~@~'::text, 1))::bigint ELSE '0'::bigint END), max(CASE WHEN (part_f_campaign_transition_detail.status_trans_category_id = 3) THEN (split_part((part_f_campaign_transition_detail.transitions_characteristics_b)::text, '~@~'::text, 1))::bigint ELSE '0'::bigint END), sum(part_f_campaign_transition_detail.target_audience_transitions), sum(part_f_campaign_transition_detail.control_group_transitions), sum(part_f_campaign_transition_detail.target_audience_entries), sum(part_f_campaign_transition_detail.control_group_entries), sum(part_f_campaign_transition_detail.target_audience_incentivized), sum(part_f_campaign_transition_detail.control_group_incentivized), sum(part_f_campaign_transition_detail.target_audience_positive), sum(part_f_campaign_transition_detail.control_group_positive), sum(part_f_campaign_transition_detail.target_audience_negative), sum(part_f_campaign_transition_detail.control_group_negative), sum(part_f_campaign_transition_detail.target_audience_exits), sum(part_f_campaign_transition_detail.control_group_exits), sum(part_f_campaign_transition_detail.target_audience_revenue), sum(part_f_campaign_transition_detail.control_group_revenue), sum(part_f_campaign_transition_detail.target_audience_cost), sum(part_f_campaign_transition_detail.control_group_cost), part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.contract_id
  • Group Key: part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.contract_id
  • Buffers: shared hit=3210731186 read=547166005 dirtied=101
4. 0.008 62,976,233.358 ↓ 0.0 0 1

Sort (cost=79,504.50..79,504.51 rows=1 width=150) (actual time=62,976,233.358..62,976,233.358 rows=0 loops=1)

  • Output: part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.contract_id, part_f_campaign_transition_detail.audit_time, part_f_campaign_transition_detail.status_trans_category_id, part_f_campaign_transition_detail.time_key, part_f_campaign_transition_detail.hour, part_f_campaign_transition_detail.previous_state_key, part_f_campaign_transition_detail.state_key, part_f_campaign_transition_detail.event_key, part_f_campaign_transition_detail.transitions_characteristics_a, part_f_campaign_transition_detail.transitions_characteristics_b, part_f_campaign_transition_detail.target_audience_transitions, part_f_campaign_transition_detail.control_group_transitions, part_f_campaign_transition_detail.target_audience_entries, part_f_campaign_transition_detail.control_group_entries, part_f_campaign_transition_detail.target_audience_incentivized, part_f_campaign_transition_detail.control_group_incentivized, part_f_campaign_transition_detail.target_audience_positive, part_f_campaign_transition_detail.control_group_positive, part_f_campaign_transition_detail.target_audience_negative, part_f_campaign_transition_detail.control_group_negative, part_f_campaign_transition_detail.target_audience_exits, part_f_campaign_transition_detail.control_group_exits, part_f_campaign_transition_detail.target_audience_revenue, part_f_campaign_transition_detail.control_group_revenue, part_f_campaign_transition_detail.target_audience_cost, part_f_campaign_transition_detail.control_group_cost
  • Sort Key: part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.contract_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3210731186 read=547166005 dirtied=101
5. 8,005,410.850 62,976,233.350 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.00..79,504.49 rows=1 width=150) (actual time=62,976,233.350..62,976,233.350 rows=0 loops=1)

  • Output: part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.contract_id, part_f_campaign_transition_detail.audit_time, part_f_campaign_transition_detail.status_trans_category_id, part_f_campaign_transition_detail.time_key, part_f_campaign_transition_detail.hour, part_f_campaign_transition_detail.previous_state_key, part_f_campaign_transition_detail.state_key, part_f_campaign_transition_detail.event_key, part_f_campaign_transition_detail.transitions_characteristics_a, part_f_campaign_transition_detail.transitions_characteristics_b, part_f_campaign_transition_detail.target_audience_transitions, part_f_campaign_transition_detail.control_group_transitions, part_f_campaign_transition_detail.target_audience_entries, part_f_campaign_transition_detail.control_group_entries, part_f_campaign_transition_detail.target_audience_incentivized, part_f_campaign_transition_detail.control_group_incentivized, part_f_campaign_transition_detail.target_audience_positive, part_f_campaign_transition_detail.control_group_positive, part_f_campaign_transition_detail.target_audience_negative, part_f_campaign_transition_detail.control_group_negative, part_f_campaign_transition_detail.target_audience_exits, part_f_campaign_transition_detail.control_group_exits, part_f_campaign_transition_detail.target_audience_revenue, part_f_campaign_transition_detail.control_group_revenue, part_f_campaign_transition_detail.target_audience_cost, part_f_campaign_transition_detail.control_group_cost
  • Join Filter: (((f.contract_id)::text = (part_f_campaign_transition_detail.contract_id)::text) AND ((f.customer_id)::text = (part_f_campaign_transition_detail.customer_id)::text) AND (f.campaign_key = part_f_campaign_transition_detail.campaign_key) AND (f.partition_field = part_f_campaign_transition_detail.partition_field))
  • Rows Removed by Join Filter: 76540271320
  • Buffers: shared hit=3210731186 read=547166005 dirtied=101
6. 226.320 226.320 ↓ 203,785.0 203,785 1

Seq Scan on bitelco.part_f_campaign_transition_detail (cost=0.00..9,184.00 rows=1 width=150) (actual time=0.012..226.320 rows=203,785 loops=1)

  • Output: part_f_campaign_transition_detail.time_key, part_f_campaign_transition_detail.hour, part_f_campaign_transition_detail.campaign_key, part_f_campaign_transition_detail.partition_field, part_f_campaign_transition_detail.previous_state_key, part_f_campaign_transition_detail.state_key, part_f_campaign_transition_detail.event_key, part_f_campaign_transition_detail.target_audience_transitions, part_f_campaign_transition_detail.control_group_transitions, part_f_campaign_transition_detail.target_audience_entries, part_f_campaign_transition_detail.control_group_entries, part_f_campaign_transition_detail.target_audience_incentivized, part_f_campaign_transition_detail.control_group_incentivized, part_f_campaign_transition_detail.target_audience_positive, part_f_campaign_transition_detail.control_group_positive, part_f_campaign_transition_detail.target_audience_negative, part_f_campaign_transition_detail.control_group_negative, part_f_campaign_transition_detail.target_audience_exits, part_f_campaign_transition_detail.control_group_exits, part_f_campaign_transition_detail.target_audience_revenue, part_f_campaign_transition_detail.control_group_revenue, part_f_campaign_transition_detail.target_audience_cost, part_f_campaign_transition_detail.control_group_cost, part_f_campaign_transition_detail.customer_id, part_f_campaign_transition_detail.source_event_time, part_f_campaign_transition_detail.acm_event_time, part_f_campaign_transition_detail.transition_time, part_f_campaign_transition_detail.event_info1, part_f_campaign_transition_detail.event_info2, part_f_campaign_transition_detail.event_info3, part_f_campaign_transition_detail.campaign_id, part_f_campaign_transition_detail.campaign_run, part_f_campaign_transition_detail.previous_state_id, part_f_campaign_transition_detail.state_id, part_f_campaign_transition_detail.audit_time, part_f_campaign_transition_detail.audit_id, part_f_campaign_transition_detail.transitions_characteristics_a, part_f_campaign_transition_detail.transitions_characteristics_b, part_f_campaign_transition_detail.status_trans_category_id, part_f_campaign_transition_detail.contract_id, part_f_campaign_transition_detail.c1, part_f_campaign_transition_detail.c2, part_f_campaign_transition_detail.c3, part_f_campaign_transition_detail.c4, part_f_campaign_transition_detail.c5, part_f_campaign_transition_detail.c6, part_f_campaign_transition_detail.c7, part_f_campaign_transition_detail.c8, part_f_campaign_transition_detail.c9, part_f_campaign_transition_detail.c10, part_f_campaign_transition_detail.c11, part_f_campaign_transition_detail.c12, part_f_campaign_transition_detail.c13, part_f_campaign_transition_detail.c14, part_f_campaign_transition_detail.c15, part_f_campaign_transition_detail.c16, part_f_campaign_transition_detail.c17, part_f_campaign_transition_detail.c18, part_f_campaign_transition_detail.c19, part_f_campaign_transition_detail.c20, part_f_campaign_transition_detail.c21, part_f_campaign_transition_detail.c22, part_f_campaign_transition_detail.c23, part_f_campaign_transition_detail.c24, part_f_campaign_transition_detail.c25, part_f_campaign_transition_detail.c26, part_f_campaign_transition_detail.c27, part_f_campaign_transition_detail.c28, part_f_campaign_transition_detail.c29, part_f_campaign_transition_detail.c30, part_f_campaign_transition_detail.c31, part_f_campaign_transition_detail.c32, part_f_campaign_transition_detail.c33, part_f_campaign_transition_detail.c34, part_f_campaign_transition_detail.c35, part_f_campaign_transition_detail.c36, part_f_campaign_transition_detail.c37, part_f_campaign_transition_detail.c38, part_f_campaign_transition_detail.c39, part_f_campaign_transition_detail.c40, part_f_campaign_transition_detail.c41, part_f_campaign_transition_detail.c42, part_f_campaign_transition_detail.c43, part_f_campaign_transition_detail.c44, part_f_campaign_transition_detail.c45, part_f_campaign_transition_detail.c46, part_f_campaign_transition_detail.c47, part_f_campaign_transition_detail.c48, part_f_campaign_transition_detail.c49, part_f_campaign_transition_detail.c50, part_f_campaign_transition_detail.c51, part_f_campaign_transition_detail.c52, part_f_campaign_transition_detail.c53, part_f_campaign_transition_detail.c54, part_f_campaign_transition_detail.c55, part_f_campaign_transition_detail.c56, part_f_campaign_transition_detail.c57, part_f_campaign_transition_detail.c58, part_f_campaign_transition_detail.c59, part_f_campaign_transition_detail.c60
  • Buffers: shared hit=9184
7. 8,645,986.195 54,970,596.180 ↑ 2.2 375,594 203,785

Append (cost=0.00..53,652.21 rows=833,414 width=60) (actual time=0.011..269.748 rows=375,594 loops=203,785)

  • Buffers: shared hit=3210722002 read=547166005 dirtied=101
8. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201804 f (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f.contract_id, f.customer_id, f.campaign_key, f.partition_field
9. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201805 f_1 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_1.contract_id, f_1.customer_id, f_1.campaign_key, f_1.partition_field
10. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201806 f_2 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_2.contract_id, f_2.customer_id, f_2.campaign_key, f_2.partition_field
11. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201807 f_3 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_3.contract_id, f_3.customer_id, f_3.campaign_key, f_3.partition_field
12. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201808 f_4 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_4.contract_id, f_4.customer_id, f_4.campaign_key, f_4.partition_field
13. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201809 f_5 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_5.contract_id, f_5.customer_id, f_5.campaign_key, f_5.partition_field
14. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201810 f_6 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_6.contract_id, f_6.customer_id, f_6.campaign_key, f_6.partition_field
15. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201811 f_7 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_7.contract_id, f_7.customer_id, f_7.campaign_key, f_7.partition_field
16. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201812 f_8 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_8.contract_id, f_8.customer_id, f_8.campaign_key, f_8.partition_field
17. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201901 f_9 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_9.contract_id, f_9.customer_id, f_9.campaign_key, f_9.partition_field
18. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201902 f_10 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_10.contract_id, f_10.customer_id, f_10.campaign_key, f_10.partition_field
19. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201903 f_11 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_11.contract_id, f_11.customer_id, f_11.campaign_key, f_11.partition_field
20. 0.000 0.000 ↓ 0.0 0 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201904 f_12 (cost=0.00..10.80 rows=80 width=276) (actual time=0.000..0.000 rows=0 loops=203,785)

  • Output: f_12.contract_id, f_12.customer_id, f_12.campaign_key, f_12.partition_field
21. 46,324,609.985 46,324,609.985 ↑ 2.2 375,594 203,785

Seq Scan on bitelco.f_campaign_transition_characteristics_p201905 f_13 (cost=0.00..49,263.20 rows=831,420 width=60) (actual time=0.005..227.321 rows=375,594 loops=203,785)

  • Output: f_13.contract_id, f_13.customer_id, f_13.campaign_key, f_13.partition_field
  • Buffers: shared hit=3210722002 read=547166005 dirtied=101
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on bitelco.f_campaign_transition_characteristics_p201906 f_14 (cost=0.00..59.94 rows=794 width=59) (never executed)

  • Output: f_14.contract_id, f_14.customer_id, f_14.campaign_key, f_14.partition_field
23. 0.000 0.000 ↓ 0.0 0

Seq Scan on bitelco.f_campaign_transition_characteristics_p201907 f_15 (cost=0.00..10.80 rows=80 width=276) (never executed)

  • Output: f_15.contract_id, f_15.customer_id, f_15.campaign_key, f_15.partition_field
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on bitelco.f_campaign_transition_characteristics_p201908 f_16 (cost=0.00..10.80 rows=80 width=276) (never executed)

  • Output: f_16.contract_id, f_16.customer_id, f_16.campaign_key, f_16.partition_field