explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QI5t

Settings
# exclusive inclusive rows x rows loops node
1. 0.147 184,231.236 ↑ 1,081.1 37 1

Sort (cost=5,093,761.56..5,093,861.56 rows=40,000 width=834) (actual time=184,231.231..184,231.236 rows=37 loops=1)

  • Sort Key: intervals.intrvl
  • Sort Method: quicksort Memory: 32kB
2.          

CTE sessions

3. 0.220 4,409.659 ↑ 18.8 85 1

Unique (cost=51,083.44..51,091.45 rows=1,602 width=50) (actual time=4,409.370..4,409.659 rows=85 loops=1)

4. 1.778 4,409.439 ↑ 18.9 85 1

Sort (cost=51,083.44..51,087.45 rows=1,603 width=50) (actual time=4,409.368..4,409.439 rows=85 loops=1)

  • Sort Key: project_sessions.session_id
  • Sort Method: quicksort Memory: 36kB
5. 4,190.783 4,407.661 ↑ 18.9 85 1

Bitmap Heap Scan on project_sessions (cost=681.65..50,998.11 rows=1,603 width=50) (actual time=3,634.706..4,407.661 rows=85 loops=1)

  • Recheck Cond: ((project_id)::text = 'corebo00000000000md1pd5eom9idr5o'::text)
  • Filter: (("timestamp" >= '2019-01-01 00:00:00'::timestamp without time zone) AND ("timestamp" <= '2019-02-06 00:00:00'::timestamp without time zone) AND ((event)::text = 'add'::text))
  • Rows Removed by Filter: 1114
  • Heap Blocks: exact=705
6. 216.878 216.878 ↑ 13.9 1,199 1

Bitmap Index Scan on idx_project_sessions_project_id (cost=0.00..681.25 rows=16,625 width=0) (actual time=216.877..216.878 rows=1,199 loops=1)

  • Index Cond: ((project_id)::text = 'corebo00000000000md1pd5eom9idr5o'::text)
7.          

CTE intervals

8. 11.634 11.634 ↑ 27.8 36 1

Function Scan on intervalize (cost=0.25..10.25 rows=1,000 width=192) (actual time=11.626..11.634 rows=36 loops=1)

9.          

CTE ivr_sessions

10. 8,373.082 171,176.420 ↑ 161.4 4,018 1

Hash Join (cost=3,855,406.87..4,200,014.35 rows=648,512 width=96) (actual time=163,249.405..171,176.420 rows=4,018 loops=1)

  • Hash Cond: ((sessions.ses)::text = (impl_ivr_flow_log.session_id)::text)
11. 0.162 0.162 ↑ 18.8 85 1

CTE Scan on sessions (cost=0.00..32.04 rows=1,602 width=146) (actual time=0.002..0.162 rows=85 loops=1)

12. 20,967.138 162,803.176 ↑ 1.0 20,812,833 1

Hash (cost=3,287,318.21..3,287,318.21 rows=20,925,253 width=96) (actual time=162,803.175..162,803.176 rows=20,812,833 loops=1)

  • Buckets: 524288 Batches: 64 Memory Usage: 48986kB
13. 139,179.322 141,836.038 ↑ 1.0 20,812,833 1

Bitmap Heap Scan on impl_ivr_flow_log (cost=444,012.41..3,287,318.21 rows=20,925,253 width=96) (actual time=2,898.692..141,836.038 rows=20,812,833 loops=1)

  • Recheck Cond: ((block_start >= '2019-01-01 00:00:00'::timestamp without time zone) AND (block_start <= '2019-02-06 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=717076
14. 2,656.716 2,656.716 ↑ 1.0 20,813,275 1

Bitmap Index Scan on idx_time_ivrlog (cost=0.00..438,781.10 rows=20,925,253 width=0) (actual time=2,656.716..2,656.716 rows=20,813,275 loops=1)

  • Index Cond: ((block_start >= '2019-01-01 00:00:00'::timestamp without time zone) AND (block_start <= '2019-02-06 00:00:00'::timestamp without time zone))
15.          

CTE first_transf

16. 1.491 6,544.454 ↑ 22.3 85 1

GroupAggregate (cost=13,736.18..14,284.86 rows=1,892 width=99) (actual time=6,543.072..6,544.454 rows=85 loops=1)

  • Group Key: qc.session_id
17. 0.850 6,542.963 ↑ 7.6 248 1

Sort (cost=13,736.18..13,740.91 rows=1,892 width=83) (actual time=6,542.930..6,542.963 rows=248 loops=1)

  • Sort Key: qc.session_id
  • Sort Method: quicksort Memory: 59kB
18. 0.767 6,542.113 ↑ 7.6 248 1

Nested Loop (cost=0.56..13,633.20 rows=1,892 width=83) (actual time=4,445.425..6,542.113 rows=248 loops=1)

19. 4,409.801 4,409.801 ↑ 18.8 85 1

CTE Scan on sessions sessions_1 (cost=0.00..32.04 rows=1,602 width=146) (actual time=4,409.373..4,409.801 rows=85 loops=1)

20. 2,131.545 2,131.545 ↓ 3.0 3 85

Index Scan using idx_qc_session_id on queued_calls qc (cost=0.56..8.48 rows=1 width=83) (actual time=20.467..25.077 rows=3 loops=85)

  • Index Cond: ((session_id)::text = (sessions_1.ses)::text)
21.          

CTE predetail

22. 4.452 184,216.311 ↑ 20.1 85 1

Result (cost=109,320.60..776,494.50 rows=1,711 width=438) (actual time=183,059.115..184,216.311 rows=85 loops=1)

23. 0.545 11,817.467 ↑ 20.1 85 1

Sort (cost=109,320.60..109,324.88 rows=1,711 width=132) (actual time=11,817.404..11,817.467 rows=85 loops=1)

  • Sort Key: qc_1.enqueued_time
  • Sort Method: quicksort Memory: 36kB
24. 0.822 11,816.922 ↑ 20.1 85 1

Nested Loop (cost=282.72..109,228.72 rows=1,711 width=132) (actual time=8,691.265..11,816.922 rows=85 loops=1)

25. 0.544 8,647.385 ↑ 14.8 85 1

Hash Left Join (cost=282.16..17,298.48 rows=1,255 width=107) (actual time=8,646.061..8,647.385 rows=85 loops=1)

  • Hash Cond: ((qc_1.session_id)::text = (first_transf.session_id)::text)
26. 2,054.177 2,102.243 ↑ 14.8 85 1

Bitmap Heap Scan on queued_calls qc_1 (cost=220.67..16,937.35 rows=1,255 width=59) (actual time=2,101.426..2,102.243 rows=85 loops=1)

  • Recheck Cond: ((project_id)::text = 'corebo00000000000md1pd5eom9idr5o'::text)
  • Filter: ((enqueued_time >= '2019-01-01 00:00:00'::timestamp without time zone) AND (enqueued_time <= '2019-02-06 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 516
  • Heap Blocks: exact=531
27. 48.066 48.066 ↑ 8.8 601 1

Bitmap Index Scan on idx_qc_project_id (cost=0.00..220.36 rows=5,307 width=0) (actual time=48.066..48.066 rows=601 loops=1)

  • Index Cond: ((project_id)::text = 'corebo00000000000md1pd5eom9idr5o'::text)
28. 0.056 6,544.598 ↑ 22.3 85 1

Hash (cost=37.84..37.84 rows=1,892 width=194) (actual time=6,544.598..6,544.598 rows=85 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 25kB
29. 6,544.542 6,544.542 ↑ 22.3 85 1

CTE Scan on first_transf (cost=0.00..37.84 rows=1,892 width=194) (actual time=6,543.080..6,544.542 rows=85 loops=1)

30. 3,168.715 3,168.715 ↑ 6.0 1 85

Index Scan using idx_call_legs_session_id on call_legs calllength (cost=0.56..73.19 rows=6 width=75) (actual time=22.754..37.279 rows=1 loops=85)

  • Index Cond: ((session_id)::text = (qc_1.session_id)::text)
  • Filter: (leg_id = 1)
  • Rows Removed by Filter: 4
31.          

SubPlan (forResult)

32. 493.340 493.340 ↓ 0.0 0 85

Index Scan using mq_interact_sid on impl_mq_interaction_log imil (cost=0.42..8.45 rows=1 width=0) (actual time=5.804..5.804 rows=0 loops=85)

  • Index Cond: ((session_id)::text = (qc_1.session_id)::text)
  • Filter: (((function_id)::text = 'SearchClientInfo'::text) AND ((((respbody -> 'data'::text) -> 'searchClientInfoResponse'::text) ->> 'clientID'::text) IS NOT NULL))
  • Rows Removed by Filter: 0
33. 0.000 0.000 ↓ 0.0 0

Seq Scan on impl_mq_interaction_log imil_1 (cost=0.00..16,072.66 rows=1,045 width=32) (never executed)

  • Filter: (((function_id)::text = 'SearchClientInfo'::text) AND ((((respbody -> 'data'::text) -> 'searchClientInfoResponse'::text) ->> 'clientID'::text) IS NOT NULL))
34. 35.955 35.955 ↓ 0.0 0 85

Index Scan using mq_interact_sid on impl_mq_interaction_log imil_2 (cost=0.42..8.45 rows=1 width=0) (actual time=0.423..0.423 rows=0 loops=85)

  • Index Cond: ((session_id)::text = (qc_1.session_id)::text)
  • Filter: (((function_id)::text = ANY ('{GetCardFinInfo,GetContractFinInfo}'::text[])) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer = 0))
  • Rows Removed by Filter: 1
35. 0.000 0.000 ↓ 0.0 0

Seq Scan on impl_mq_interaction_log imil_3 (cost=0.00..16,697.66 rows=1 width=32) (never executed)

  • Filter: (((function_id)::text = ANY ('{GetCardFinInfo,GetContractFinInfo}'::text[])) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer = 0))
36. 38.930 38.930 ↓ 0.0 0 85

Index Scan using mq_interact_sid on impl_mq_interaction_log imil_4 (cost=0.42..8.45 rows=1 width=0) (actual time=0.458..0.458 rows=0 loops=85)

  • Index Cond: ((session_id)::text = (qc_1.session_id)::text)
  • Filter: (((function_id)::text = ANY ('{GetCardFinInfo,GetContractFinInfo}'::text[])) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer <> 0))
  • Rows Removed by Filter: 1
37. 0.000 0.000 ↓ 0.0 0

Seq Scan on impl_mq_interaction_log imil_5 (cost=0.00..16,697.66 rows=104 width=32) (never executed)

  • Filter: (((function_id)::text = ANY ('{GetCardFinInfo,GetContractFinInfo}'::text[])) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer <> 0))
38. 0.000 0.000 ↓ 0.0 0

CTE Scan on ivr_sessions iifl (cost=0.00..17,023.44 rows=49 width=0) (never executed)

  • Filter: (((session_id)::text = (qc_1.session_id)::text) AND (node_id = ANY ('{ivr/fin_info_closed.iscr-ef14cb91852c4dbcb5fff5a583e12fa5,ivr/fin_info_closed.iscr-f9ce5cf1f04943b18b9e187e51b471b1,ivr/fin_info_closed.iscr-2e19bfd191d64204a4b98063417d6a25}'::text[])))
39. 171,180.507 171,180.507 ↓ 0.0 0 1

CTE Scan on ivr_sessions iifl_1 (cost=0.00..15,402.16 rows=9,728 width=32) (actual time=171,180.507..171,180.507 rows=0 loops=1)

  • Filter: (node_id = ANY ('{ivr/fin_info_closed.iscr-ef14cb91852c4dbcb5fff5a583e12fa5,ivr/fin_info_closed.iscr-f9ce5cf1f04943b18b9e187e51b471b1,ivr/fin_info_closed.iscr-2e19bfd191d64204a4b98063417d6a25}'::text[]))
  • Rows Removed by Filter: 4018
40. 5.610 5.610 ↓ 0.0 0 85

Index Scan using mq_interact_sid on impl_mq_interaction_log imil_6 (cost=0.42..8.45 rows=1 width=0) (actual time=0.066..0.066 rows=0 loops=85)

  • Index Cond: ((session_id)::text = (qc_1.session_id)::text)
  • Filter: (((function_id)::text = 'SetCardBlock'::text) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer = 0))
  • Rows Removed by Filter: 1
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on impl_mq_interaction_log imil_7 (cost=0.00..16,697.66 rows=1 width=32) (never executed)

  • Filter: (((function_id)::text = 'SetCardBlock'::text) AND (((((respbody -> 'header'::text) -> 'resultInfo'::text) ->> 'code'::text))::integer = 0))
42. 640.050 640.050 ↑ 1.0 1 85

Index Scan using queued_calls_pkey on queued_calls qc3 (cost=0.43..8.45 rows=1 width=33) (actual time=7.529..7.530 rows=1 loops=85)

  • Index Cond: (id = first_transf.min_id)
43.          

CTE detail

44. 184,216.977 184,216.977 ↑ 20.1 85 1

CTE Scan on predetail (cost=0.00..68.44 rows=1,711 width=990) (actual time=183,059.140..184,216.977 rows=85 loops=1)

45. 0.776 184,231.089 ↑ 1,081.1 37 1

HashAggregate (cost=48,040.16..48,740.16 rows=40,000 width=834) (actual time=184,230.518..184,231.089 rows=37 loops=1)

  • Group Key: intervals.intrvl, 'buddy'::text, detail.incom_number, detail.authorized_client_word, NULL::text, NULL::character varying
46. 0.758 184,230.313 ↑ 2,236.6 85 1

Nested Loop (cost=0.00..42,812.11 rows=190,111 width=944) (actual time=184,229.091..184,230.313 rows=85 loops=1)

  • Join Filter: ((detail.enqueued_time >= intervals.p_begin) AND (detail.enqueued_time <= intervals.p_end))
  • Rows Removed by Join Filter: 2975
47. 11.651 11.651 ↑ 27.8 36 1

CTE Scan on intervals (cost=0.00..20.00 rows=1,000 width=192) (actual time=11.629..11.651 rows=36 loops=1)

48. 184,217.904 184,217.904 ↑ 20.1 85 36

CTE Scan on detail (cost=0.00..34.22 rows=1,711 width=680) (actual time=5,084.977..5,117.164 rows=85 loops=36)