explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Cpch

Settings
# exclusive inclusive rows x rows loops node
1. 2.818 131,262.078 ↓ 92.0 92 1

Nested Loop Left Join (cost=3.86..453,089.11 rows=1 width=961) (actual time=1,458.128..131,262.078 rows=92 loops=1)

2. 0.895 131,206.820 ↓ 92.0 92 1

Nested Loop Left Join (cost=3.30..453,086.52 rows=1 width=960) (actual time=1,456.566..131,206.820 rows=92 loops=1)

3. 1,401.216 131,196.449 ↓ 92.0 92 1

Nested Loop Left Join (cost=2.86..453,084.08 rows=1 width=943) (actual time=1,456.551..131,196.449 rows=92 loops=1)

  • Join Filter: (tm.call_leg_id = i.call_leg_id)
  • Rows Removed by Join Filter: 16219324
4. 0.838 65.113 ↓ 92.0 92 1

Nested Loop Left Join (cost=1.88..685.56 rows=1 width=877) (actual time=1.127..65.113 rows=92 loops=1)

5. 0.579 57.927 ↓ 92.0 92 1

Nested Loop Left Join (cost=1.88..682.97 rows=1 width=841) (actual time=1.063..57.927 rows=92 loops=1)

6. 0.383 47.320 ↓ 92.0 92 1

Nested Loop (cost=1.31..680.39 rows=1 width=825) (actual time=0.973..47.320 rows=92 loops=1)

7. 0.700 44.269 ↓ 92.0 92 1

Nested Loop (cost=0.87..677.95 rows=1 width=816) (actual time=0.943..44.269 rows=92 loops=1)

8. 0.640 42.465 ↓ 92.0 92 1

Nested Loop (cost=0.44..675.51 rows=1 width=807) (actual time=0.926..42.465 rows=92 loops=1)

9. 0.053 0.053 ↑ 2.7 3 1

Index Only Scan using customer_closure_key on customer_closure closure (cost=0.44..2.58 rows=8 width=8) (actual time=0.049..0.053 rows=3 loops=1)

  • Index Cond: (parent_customer_id = 2011274)
  • Heap Fetches: 0
10. 0.099 41.772 ↑ 1.5 31 3

Append (cost=0.00..83.64 rows=48 width=812) (actual time=0.679..13.924 rows=31 loops=3)

11. 0.003 0.003 ↓ 0.0 0 3

Seq Scan on call_leg i (cost=0.00..0.00 rows=1 width=1,211) (actual time=0.001..0.001 rows=0 loops=3)

  • Filter: ((end_time >= '2020-03-15 00:00:00+00'::timestamp with time zone) AND (end_time < '2020-03-16 00:00:0000'::timestamp with time zone) AND ((direction)::text = 'inbound'::text) AND (closure.child_customer_id = advertiser_id) AND ((((source_system)::text = 'MCA'::text) AND ((source_entity)::text = 'MCA_CALL'::text)) OR (((source_system)::text = 'DCM'::text) AND ((source_entity)::text = 'CALLS'::text)) OR (((source_system)::text = 'PFC'::text) AND ((source_entity)::text = 'ALL_CALL'::text))))
12. 41.670 41.670 ↑ 1.5 31 3

Index Scan using call_leg_2020_03_advertiser_id_end_time_idx on call_leg_2020_03 i_1 (cost=0.56..83.64 rows=47 width=803) (actual time=0.674..13.890 rows=31 loops=3)

  • Index Cond: ((advertiser_id = closure.child_customer_id) AND (end_time >= '2020-03-15 00:00:00+00'::timestamp with time zone) AND (end_time < '2020-03-16 00:00:00+00'::timestamp with time zone))
  • Filter: (((direction)::text = 'inbound'::text) AND ((((source_system)::text = 'MCA'::text) AND ((source_entity)::text = 'MCA_CALL'::text)) OR (((source_system)::text = 'DCM'::text) AND ((source_entity)::text = 'CALLS'::text)) OR (((source_system)::text = 'PFC'::text) AND ((source_entity)::text = 'ALL_CALL'::text))))
13. 1.104 1.104 ↑ 1.0 1 92

Index Scan using phone_number_pkey on phone_number outbound (cost=0.43..2.43 rows=1 width=17) (actual time=0.011..0.012 rows=1 loops=92)

  • Index Cond: (phone_number_id = i.outbound_phone_number_id)
14. 2.668 2.668 ↑ 1.0 1 92

Index Scan using phone_number_pkey on phone_number inbound (cost=0.43..2.43 rows=1 width=17) (actual time=0.028..0.029 rows=1 loops=92)

  • Index Cond: (phone_number_id = i.inbound_phone_number_id)
15. 10.028 10.028 ↑ 1.0 1 92

Index Scan using clm_call_leg_id_from_match_type on call_leg_match clm (cost=0.57..2.57 rows=1 width=32) (actual time=0.105..0.109 rows=1 loops=92)

  • Index Cond: ((i.call_leg_id = call_leg_id_from) AND ((match_type)::text = 'EXTERNAL'::text))
16. 0.460 6.348 ↑ 2.0 1 92

Append (cost=0.00..2.56 rows=2 width=78) (actual time=0.046..0.069 rows=1 loops=92)

17. 0.092 0.092 ↓ 0.0 0 92

Seq Scan on call_leg e (cost=0.00..0.00 rows=1 width=103) (actual time=0.001..0.001 rows=0 loops=92)

  • Filter: ((end_time >= '2020-03-14 22:50:00+00'::timestamp with time zone) AND (end_time < '2020-03-16 01:10:00+00'::timestamp with time zone) AND (call_leg_id = clm.call_leg_id_to))
18. 5.796 5.796 ↑ 1.0 1 92

Index Scan using call_leg_2020_03_pkey on call_leg_2020_03 e_1 (cost=0.56..2.56 rows=1 width=52) (actual time=0.040..0.063 rows=1 loops=92)

  • Index Cond: (call_leg_id = clm.call_leg_id_to)
  • Filter: ((end_time >= '2020-03-14 22:50:00+00'::timestamp with time zone) AND (end_time < '2020-03-16 01:10:00+00'::timestamp with time zone))
19. 12,364.156 129,730.120 ↓ 1.5 176,297 92

GroupAggregate (cost=0.98..449,783.24 rows=116,235 width=36) (actual time=0.030..1,410.110 rows=176,297 loops=92)

  • Group Key: tm.call_leg_id
20. 1,178.888 117,365.964 ↓ 1.0 220,067 92

Nested Loop (cost=0.98..445,030.69 rows=219,974 width=36) (actual time=0.017..1,275.717 rows=220,067 loops=92)

21. 14,956.256 14,956.256 ↓ 1.0 220,067 92

Index Scan using transaction_match_call_leg_id_idx on transaction_match tm (cost=0.42..7,116.03 rows=219,974 width=24) (actual time=0.009..162.568 rows=220,067 loops=92)

22. 101,230.820 101,230.820 ↑ 1.0 1 20,246,164

Index Scan using transaction_pkey on transaction t (cost=0.56..1.98 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=20,246,164)

  • Index Cond: (transaction_id = tm.transaction_id)
23. 9.476 9.476 ↑ 1.0 1 92

Index Scan using call_tracking_entity_pkey on call_tracking_entity cte (cost=0.43..2.43 rows=1 width=25) (actual time=0.102..0.103 rows=1 loops=92)

  • Index Cond: (call_tracking_entity_id = i.call_tracking_entity_id)
24. 52.440 52.440 ↓ 0.0 0 92

Index Scan using call_leg_projection_new_pkey on call_leg_projection clp (cost=0.56..2.56 rows=1 width=17) (actual time=0.570..0.570 rows=0 loops=92)

  • Index Cond: (call_leg_id = i.call_leg_id)