explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mexU

Settings
# exclusive inclusive rows x rows loops node
1. 2.136 158,797.707 ↓ 1.9 383 1

Unique (cost=16,668,727.90..16,668,737.75 rows=197 width=349) (actual time=158,795.315..158,797.707 rows=383 loops=1)

  • Buffers: shared hit=4,401,446 read=8,461,939
2. 15.676 158,795.571 ↓ 14.7 2,895 1

Sort (cost=16,668,727.90..16,668,728.39 rows=197 width=349) (actual time=158,795.314..158,795.571 rows=2,895 loops=1)

  • Sort Key: dsr.service_request_nbr, dsr.service_request_id, dsr.agreement_id, ccc.customer_case_nbr, cch.name, ccc.customer_case_id, ccc.client_account_id, cch.client_channel_id, enrolla.state_province_code, ast.mobile_device_nbr, ca.external_reference_id, c.full_name, ((timezone('UTC'::text, ccc.customer_case_start_date))::date), (replace((sr.assetfailuredescriptivetext)::text, 'Ì'::text, ''::text)), ccc.incident_type_code, ccc.customer_case_status_code, dsr.service_request_status_code, dsr.asset_id, w.incidentname
  • Sort Method: quicksort Memory: 1,695kB
  • Buffers: shared hit=4,401,446 read=8,461,939
3. 13,703.418 158,779.895 ↓ 14.7 2,895 1

Hash Join (cost=5,853,720.10..16,668,720.39 rows=197 width=349) (actual time=48,213.918..158,779.895 rows=2,895 loops=1)

  • Hash Cond: (il.customer_case_id = ccc.customer_case_id)
  • Buffers: shared hit=4,401,446 read=8,461,939
4. 118,111.733 118,111.733 ↓ 1.0 132,823,917 1

Seq Scan on interaction_interaction_line il (cost=0.00..10,319,242.20 rows=132,201,239 width=33) (actual time=0.022..118,111.733 rows=132,823,917 loops=1)

  • Filter: ((channel_name)::text <> 'INSTORE'::text)
  • Rows Removed by Filter: 6,898,463
  • Buffers: shared hit=116,291 read=8,461,911
5. 0.466 26,964.744 ↓ 10.2 417 1

Hash (cost=5,853,719.59..5,853,719.59 rows=41 width=368) (actual time=26,964.744..26,964.744 rows=417 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 181kB
  • Buffers: shared hit=4,285,155 read=28
6. 0.381 26,964.278 ↓ 10.2 417 1

Nested Loop (cost=3,824,171.94..5,853,719.59 rows=41 width=368) (actual time=26,509.729..26,964.278 rows=417 loops=1)

  • Buffers: shared hit=4,285,155 read=28
7. 0.214 26,949.302 ↓ 10.2 417 1

Hash Join (cost=3,824,171.38..5,853,461.87 rows=41 width=386) (actual time=26,509.706..26,949.302 rows=417 loops=1)

  • Hash Cond: (ca.client_channel_id = cch.client_channel_id)
  • Buffers: shared hit=4,283,069 read=19
8. 0.503 26,949.059 ↓ 10.2 417 1

Nested Loop (cost=3,824,167.05..5,853,457.42 rows=41 width=374) (actual time=26,509.670..26,949.059 rows=417 loops=1)

  • Buffers: shared hit=4,283,066 read=19
9. 0.514 26,943.135 ↓ 10.2 417 1

Nested Loop (cost=3,824,166.48..5,853,200.65 rows=41 width=331) (actual time=26,509.653..26,943.135 rows=417 loops=1)

  • Buffers: shared hit=4,280,965 read=17
10. 0.369 26,937.617 ↓ 9.5 417 1

Nested Loop Left Join (cost=3,824,165.79..5,852,912.07 rows=44 width=320) (actual time=26,509.635..26,937.617 rows=417 loops=1)

  • Buffers: shared hit=4,278,459 read=15
11. 0.566 26,926.823 ↓ 9.5 417 1

Nested Loop Left Join (cost=3,824,165.22..5,852,636.62 rows=44 width=350) (actual time=26,509.619..26,926.823 rows=417 loops=1)

  • Buffers: shared hit=4,276,374 read=9
12. 1,720.868 26,917.500 ↓ 9.5 417 1

Hash Join (cost=3,824,164.65..5,852,357.40 rows=44 width=317) (actual time=26,509.590..26,917.500 rows=417 loops=1)

  • Hash Cond: ((dsr.service_request_nbr)::text = (ch.servicerequestnumber)::text)
  • Buffers: shared hit=4,274,293 read=3
13. 7,226.726 7,226.726 ↑ 1.0 15,954,432 1

Seq Scan on customer_service_request dsr (cost=0.00..1,967,590.75 rows=16,160,417 width=118) (actual time=0.004..7,226.726 rows=15,954,432 loops=1)

  • Filter: (((service_request_type_code)::text = ANY ('{REPLREQ,RESHPREQ,TRBL,SERVICE,ACRS,REWORK}'::text[])) AND ((service_request_status_code)::text = ANY ('{CMPLTD,CNCLSYS,VOID,WORKNG,CNCLCUS,NEW,HOLD,UNSUC,FAILED,PNDVOID,PRDRTN,SUCCESSFUL}'::text[])))
  • Rows Removed by Filter: 6,381,389
  • Buffers: shared hit=1,243,278
14. 0.442 17,969.906 ↓ 10.0 601 1

Hash (cost=3,824,163.90..3,824,163.90 rows=60 width=223) (actual time=17,969.906..17,969.906 rows=601 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 169kB
  • Buffers: shared hit=3,031,015 read=3
15. 1,242.846 17,969.464 ↓ 10.0 601 1

Hash Join (cost=2,486,572.49..3,824,163.90 rows=60 width=223) (actual time=17,101.919..17,969.464 rows=601 loops=1)

  • Hash Cond: ((ccc.customer_case_nbr)::text = (ch.customercasenumber)::text)
  • Buffers: shared hit=3,031,015 read=3
16. 4,815.606 4,815.606 ↑ 1.0 11,824,284 1

Seq Scan on customer_customer_case ccc (cost=0.00..1,293,036.30 rows=11,881,204 width=131) (actual time=0.003..4,815.606 rows=11,824,284 loops=1)

  • Filter: ((customer_case_status_code)::text = ANY ('{CMPLTD,WORKING,NEW,REOPEN,CNCLCUS}'::text[]))
  • Rows Removed by Filter: 6,478,978
  • Buffers: shared hit=996,128
17. 0.492 11,911.012 ↓ 8.1 746 1

Hash (cost=2,486,571.34..2,486,571.34 rows=92 width=103) (actual time=11,911.012..11,911.012 rows=746 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 115kB
  • Buffers: shared hit=2,034,887 read=3
18. 2,233.329 11,910.520 ↓ 8.1 746 1

Hash Join (cost=1,530,464.79..2,486,571.34 rows=92 width=103) (actual time=7,678.023..11,910.520 rows=746 loops=1)

  • Hash Cond: ((sr.servicerequestnumber)::text = (ch.servicerequestnumber)::text)
  • Buffers: shared hit=2,034,887 read=3
19. 2,386.865 2,386.865 ↓ 1.0 16,324,149 1

Seq Scan on hrz_cepcache_d_servicerequest sr (cost=0.00..895,122.82 rows=16,262,082 width=59) (actual time=0.003..2,386.865 rows=16,324,149 loops=1)

  • Buffers: shared hit=732,502
20. 0.430 7,290.326 ↓ 8.1 746 1

Hash (cost=1,530,463.64..1,530,463.64 rows=92 width=44) (actual time=7,290.326..7,290.326 rows=746 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 69kB
  • Buffers: shared hit=1,302,385 read=3
21. 4,328.079 7,289.896 ↓ 8.1 746 1

Hash Join (cost=66,775.14..1,530,463.64 rows=92 width=44) (actual time=5,363.380..7,289.896 rows=746 loops=1)

  • Hash Cond: (ch.servicerequest_id = di."servicerequest_id$")
  • Buffers: shared hit=1,302,385 read=3
22. 2,856.024 2,856.024 ↓ 1.0 16,323,198 1

Seq Scan on hrz_cepcache_d_casesrhistory ch (cost=0.00..1,402,725.24 rows=16,256,624 width=33) (actual time=0.002..2,856.024 rows=16,323,198 loops=1)

  • Buffers: shared hit=1,240,159
23. 0.191 105.793 ↓ 3.7 746 1

Hash (cost=66,772.62..66,772.62 rows=202 width=31) (actual time=105.793..105.793 rows=746 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 58kB
  • Buffers: shared hit=62,226 read=3
24. 21.433 105.602 ↓ 5.7 1,149 1

Gather (cost=1,000.57..66,772.62 rows=202 width=31) (actual time=88.130..105.602 rows=1,149 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=62,226 read=3
25. 0.266 84.169 ↓ 4.6 230 5 / 5

Nested Loop (cost=0.57..65,752.42 rows=50 width=31) (actual time=81.298..84.169 rows=230 loops=5)

  • Buffers: shared hit=62,226 read=3
26. 81.375 81.375 ↓ 4.6 230 5 / 5

Parallel Seq Scan on hrz_cepcache_d_wtd_incident w (cost=0.00..65,323.17 rows=50 width=32) (actual time=81.291..81.375 rows=230 loops=5)

  • Filter: ("time_created$" >= (CURRENT_DATE - 1))
  • Rows Removed by Filter: 404,135
  • Buffers: shared hit=56,477
27. 2.528 2.528 ↑ 1.0 1 1,149 / 5

Index Scan using hrz_cepcache_d_interaction_pkey on hrz_cepcache_d_interaction di (cost=0.57..8.59 rows=1 width=20) (actual time=0.011..0.011 rows=1 loops=1,149)

  • Index Cond: ("id$" = w."parent$_id$")
  • Buffers: shared hit=5,749 read=3
28. 8.757 8.757 ↑ 1.0 1 417

Index Scan using asset_agreement_pkey on asset_agreement agmt (cost=0.57..6.35 rows=1 width=66) (actual time=0.021..0.021 rows=1 loops=417)

  • Index Cond: (dsr.agreement_id = agreement_id)
  • Buffers: shared hit=2,081 read=6
29. 10.425 10.425 ↑ 1.0 1 417

Index Scan using address_address_id_idx on reference_address enrolla (cost=0.57..6.26 rows=1 width=36) (actual time=0.025..0.025 rows=1 loops=417)

  • Index Cond: (agmt.address_id = address_id)
  • Buffers: shared hit=2,085 read=6
30. 5.004 5.004 ↑ 1.0 1 417

Index Scan using asset_asset_pkey on asset_asset ast (cost=0.70..6.56 rows=1 width=44) (actual time=0.012..0.012 rows=1 loops=417)

  • Index Cond: (asset_id = dsr.asset_id)
  • Buffers: shared hit=2,506 read=2
31. 5.421 5.421 ↑ 1.0 1 417

Index Scan using client_client_account_pkey on client_client_account ca (cost=0.56..6.26 rows=1 width=76) (actual time=0.013..0.013 rows=1 loops=417)

  • Index Cond: (client_account_id = ccc.client_account_id)
  • Buffers: shared hit=2,101 read=2
32. 0.016 0.029 ↑ 1.0 59 1

Hash (cost=3.59..3.59 rows=59 width=45) (actual time=0.029..0.029 rows=59 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
  • Buffers: shared hit=3
33. 0.013 0.013 ↑ 1.0 59 1

Seq Scan on client_client_channel cch (cost=0.00..3.59 rows=59 width=45) (actual time=0.002..0.013 rows=59 loops=1)

  • Buffers: shared hit=3
34. 14.595 14.595 ↑ 1.0 1 417

Index Scan using customer_customer_id_idx on customer_customer c (cost=0.56..6.29 rows=1 width=48) (actual time=0.035..0.035 rows=1 loops=417)

  • Index Cond: (customer_id = ccc.customer_id)
  • Filter: ((last_name)::text <> '%test%'::text)
  • Buffers: shared hit=2,086 read=9
Planning time : 5.474 ms
Execution time : 158,798.227 ms