explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xUAP

Settings
# exclusive inclusive rows x rows loops node
1. 8.990 24,780.062 ↑ 1.0 50 1

Limit (cost=38,576,491.52..38,576,491.65 rows=50 width=270) (actual time=24,771.039..24,780.062 rows=50 loops=1)

2. 292.080 24,771.072 ↑ 1,544.9 50 1

Sort (cost=38,576,491.52..38,576,684.64 rows=77,245 width=270) (actual time=24,771.036..24,771.072 rows=50 loops=1)

  • Sort Key: c.id DESC NULLS LAST
  • Sort Method: top-N heapsort Memory: 40kB
3. 379.848 24,478.992 ↓ 3.0 231,222 1

Hash Left Join (cost=303,653.78..38,573,925.50 rows=77,245 width=270) (actual time=7,523.197..24,478.992 rows=231,222 loops=1)

  • Hash Cond: (COALESCE(claim_batches.max_claim_batch_id, c.claim_batch_id) = claim_batch_1.id)
4. 358.018 24,097.060 ↓ 3.0 231,222 1

Hash Left Join (cost=303,548.43..38,572,564.91 rows=77,245 width=254) (actual time=7,521.102..24,097.060 rows=231,222 loops=1)

  • Hash Cond: (COALESCE(claim_batches.min_claim_batch_id, c.claim_batch_id) = claim_batch.id)
5. 364.421 23,736.850 ↓ 3.0 231,222 1

Hash Left Join (cost=303,443.07..38,571,397.44 rows=77,245 width=246) (actual time=7,518.897..23,736.850 rows=231,222 loops=1)

  • Hash Cond: (c.encounter_id = e.encounter_id)
6. 649.263 23,103.165 ↓ 3.0 231,222 1

Hash Left Join (cost=272,247.88..38,539,999.47 rows=77,245 width=214) (actual time=7,249.530..23,103.165 rows=231,222 loops=1)

  • Hash Cond: (c.id = claim_batches.claim_id)
7. 569.985 18,058.546 ↓ 3.0 231,222 1

Merge Left Join (cost=132,987.03..38,395,597.86 rows=77,245 width=206) (actual time=2,853.223..18,058.546 rows=231,222 loops=1)

  • Merge Cond: (c.id = charges.claim_id)
8. 610.025 14,117.969 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=2.53..38,260,254.45 rows=77,245 width=198) (actual time=0.908..14,117.969 rows=231,222 loops=1)

9. 651.422 13,045.500 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=2.25..38,236,332.54 rows=77,245 width=189) (actual time=0.900..13,045.500 rows=231,222 loops=1)

10. 596.080 11,700.412 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=1.83..38,170,465.85 rows=77,245 width=164) (actual time=0.890..11,700.412 rows=231,222 loops=1)

11. 624.395 10,410.666 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=1.53..38,130,624.12 rows=77,245 width=162) (actual time=0.882..10,410.666 rows=231,222 loops=1)

12. 680.990 9,092.605 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=1.11..38,081,329.65 rows=77,245 width=147) (actual time=0.870..9,092.605 rows=231,222 loops=1)

13. 793.225 7,949.171 ↓ 3.0 231,222 1

Nested Loop Left Join (cost=0.84..38,058,576.99 rows=77,245 width=116) (actual time=0.865..7,949.171 rows=231,222 loops=1)

14. 900.640 6,462.280 ↓ 3.0 231,222 1

Index Scan Backward using idx_claim_id on claim c (cost=0.42..38,011,646.31 rows=77,245 width=108) (actual time=0.857..6,462.280 rows=231,222 loops=1)

  • Filter: ((SubPlan 2) >= 0.00)
  • Rows Removed by Filter: 513
15.          

SubPlan (for Index Scan Backward)

16. 926.940 5,561.640 ↑ 1.0 1 231,735

Aggregate (cost=163.93..163.94 rows=1 width=32) (actual time=0.024..0.024 rows=1 loops=231,735)

17. 1,728.873 4,634.700 ↓ 3.0 3 231,735

Nested Loop (cost=44.22..163.93 rows=1 width=3) (actual time=0.009..0.020 rows=3 loops=231,735)

18. 1,158.675 1,158.675 ↑ 1.0 3 231,735

Index Only Scan using idx_claim_charge_claim_charge on claim_charge (cost=0.42..8.48 rows=3 width=4) (actual time=0.003..0.005 rows=3 loops=231,735)

  • Index Cond: (claim_id = c.id)
  • Heap Fetches: 64964
19. 1,746.759 1,747.152 ↑ 1.0 1 582,384

Index Scan using chargeid on charge charge_1 (cost=43.79..51.82 rows=1 width=7) (actual time=0.003..0.003 rows=1 loops=582,384)

  • Index Cond: (id = claim_charge.charge_id)
  • Filter: (active AND (NOT (hashed SubPlan 1)) AND (parent_id = 0))
  • Rows Removed by Filter: 0
20.          

SubPlan (for Index Scan)

21. 0.393 0.393 ↑ 1.1 506 1

Index Only Scan using charge_parent_id_idx1 on charge (cost=0.42..41.98 rows=557 width=4) (actual time=0.010..0.393 rows=506 loops=1)

  • Index Cond: (parent_id > 0)
  • Heap Fetches: 37
22. 693.666 693.666 ↑ 1.0 1 231,222

Index Scan using policyid on policy pol (cost=0.42..0.61 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=231,222)

  • Index Cond: (id = c.policy_id)
23. 462.444 462.444 ↑ 1.0 1 231,222

Index Scan using planid on plan pl (cost=0.28..0.29 rows=1 width=35) (actual time=0.002..0.002 rows=1 loops=231,222)

  • Index Cond: (id = c.plan_id)
24. 693.666 693.666 ↑ 1.0 1 231,222

Index Scan using patientid on patient pat (cost=0.42..0.64 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=231,222)

  • Index Cond: (id = (c.patact_id / 100))
25. 693.666 693.666 ↑ 1.0 1 231,222

Index Scan using accountid on account acc (cost=0.29..0.52 rows=1 width=6) (actual time=0.003..0.003 rows=1 loops=231,222)

  • Index Cond: (id = c.patact_id)
26. 693.666 693.666 ↑ 1.0 1 231,222

Index Scan using encounter_pkey on encounter enc (cost=0.42..0.85 rows=1 width=29) (actual time=0.003..0.003 rows=1 loops=231,222)

  • Index Cond: (c.encounter_id = id)
27. 462.444 462.444 ↑ 1.0 1 231,222

Index Scan using providerid on provider pr (cost=0.29..0.31 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=231,222)

  • Index Cond: (id = enc.billing_id)
28. 310.765 3,370.592 ↓ 1.2 234,512 1

Materialize (cost=132,984.50..133,984.10 rows=199,920 width=12) (actual time=2,852.307..3,370.592 rows=234,512 loops=1)

29. 422.899 3,059.827 ↓ 1.2 234,512 1

Sort (cost=132,984.50..133,484.30 rows=199,920 width=12) (actual time=2,852.304..3,059.827 rows=234,512 loops=1)

  • Sort Key: charges.claim_id DESC
  • Sort Method: external merge Disk: 5072kB
30. 311.811 2,636.928 ↓ 1.2 234,512 1

Subquery Scan on charges (cost=54,366.65..111,962.98 rows=199,920 width=12) (actual time=997.555..2,636.928 rows=234,512 loops=1)

31. 493.357 2,325.117 ↓ 1.2 234,512 1

Finalize GroupAggregate (cost=54,366.65..109,963.78 rows=199,920 width=16) (actual time=997.553..2,325.117 rows=234,512 loops=1)

  • Group Key: cc.claim_id
32. 0.000 1,831.760 ↓ 1.0 417,824 1

Gather Merge (cost=54,366.65..104,965.78 rows=399,840 width=12) (actual time=997.537..1,831.760 rows=417,824 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
33. 787.509 4,226.196 ↑ 1.4 139,275 3

Partial GroupAggregate (cost=53,366.62..57,814.30 rows=199,920 width=12) (actual time=992.613..1,408.732 rows=139,275 loops=3)

  • Group Key: cc.claim_id
34. 1,018.113 3,438.687 ↑ 1.2 196,314 3

Sort (cost=53,366.62..53,978.74 rows=244,848 width=12) (actual time=992.602..1,146.229 rows=196,314 loops=3)

  • Sort Key: cc.claim_id
  • Sort Method: external merge Disk: 3440kB
  • Worker 0: Sort Method: external merge Disk: 3496kB
  • Worker 1: Sort Method: external merge Disk: 3504kB
35. 1,070.499 2,420.574 ↑ 1.2 196,314 3

Parallel Hash Join (cost=17,030.57..27,264.86 rows=244,848 width=12) (actual time=617.735..806.858 rows=196,314 loops=3)

  • Hash Cond: (cc.charge_id = c_1.id)
36. 492.246 492.246 ↑ 1.2 196,448 3

Parallel Seq Scan on claim_charge cc (cost=0.00..6,799.68 rows=245,560 width=8) (actual time=0.011..164.082 rows=196,448 loops=3)

  • Filter: (NOT deleted)
  • Rows Removed by Filter: 7
37. 368.442 857.829 ↑ 1.2 142,455 3

Parallel Hash (cost=13,935.02..13,935.02 rows=178,044 width=12) (actual time=285.942..285.943 rows=142,455 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3168kB
38. 489.387 489.387 ↑ 1.2 142,455 3

Parallel Seq Scan on charge c_1 (cost=0.00..13,935.02 rows=178,044 width=12) (actual time=0.017..163.129 rows=142,455 loops=3)

  • Filter: (active AND (NOT reversed) AND (parent_id = 0))
  • Rows Removed by Filter: 394
39. 178.494 4,395.356 ↓ 1.9 219,252 1

Hash (cost=137,262.35..137,262.35 rows=114,919 width=12) (actual time=4,395.355..4,395.356 rows=219,252 loops=1)

  • Buckets: 131072 (originally 131072) Batches: 4 (originally 2) Memory Usage: 3343kB
40. 279.695 4,216.862 ↓ 1.9 219,252 1

Subquery Scan on claim_batches (cost=0.43..137,262.35 rows=114,919 width=12) (actual time=0.035..4,216.862 rows=219,252 loops=1)

41. 1,842.238 3,937.167 ↓ 1.9 219,252 1

GroupAggregate (cost=0.43..136,113.16 rows=114,919 width=12) (actual time=0.033..3,937.167 rows=219,252 loops=1)

  • Group Key: claim_history.claim_id
42. 2,094.929 2,094.929 ↓ 1.0 2,197,906 1

Index Scan using claim_history_claim_id_idx on claim_history (cost=0.43..118,480.20 rows=2,197,837 width=8) (actual time=0.019..2,094.929 rows=2,197,906 loops=1)

43. 18.674 269.264 ↑ 2.0 23,169 1

Hash (cost=30,617.57..30,617.57 rows=46,210 width=36) (actual time=269.263..269.264 rows=23,169 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1504kB
44. 29.538 250.590 ↑ 2.0 23,169 1

Subquery Scan on e (cost=29,143.70..30,617.57 rows=46,210 width=36) (actual time=112.698..250.590 rows=23,169 loops=1)

45. 66.690 221.052 ↑ 2.0 23,169 1

GroupAggregate (cost=29,143.70..30,155.47 rows=46,210 width=36) (actual time=112.697..221.052 rows=23,169 loops=1)

  • Group Key: disbursement.encounter_id
46. 96.477 154.362 ↓ 1.0 59,813 1

Sort (cost=29,143.70..29,288.41 rows=57,887 width=10) (actual time=112.606..154.362 rows=59,813 loops=1)

  • Sort Key: disbursement.encounter_id
  • Sort Method: external merge Disk: 1200kB
47. 54.903 57.885 ↓ 1.0 59,813 1

Bitmap Heap Scan on disbursement (cost=1,164.71..24,564.56 rows=57,887 width=10) (actual time=3.599..57.885 rows=59,813 loops=1)

  • Recheck Cond: ((claim_id = 0) OR (claim_id IS NULL))
  • Filter: active
  • Heap Blocks: exact=4798
48. 0.001 2.982 ↓ 0.0 0 1

BitmapOr (cost=1,164.71..1,164.71 rows=57,988 width=0) (actual time=2.981..2.982 rows=0 loops=1)

49. 2.883 2.883 ↓ 1.1 58,015 1

Bitmap Index Scan on idx_disbursement_claim (cost=0.00..1,077.61 rows=55,091 width=0) (actual time=2.881..2.883 rows=58,015 loops=1)

  • Index Cond: (claim_id = 0)
50. 0.098 0.098 ↑ 1.1 2,598 1

Bitmap Index Scan on idx_disbursement_claim (cost=0.00..58.16 rows=2,897 width=0) (actual time=0.096..0.098 rows=2,598 loops=1)

  • Index Cond: (claim_id IS NULL)
51. 1.014 2.192 ↑ 1.0 1,438 1

Hash (cost=87.38..87.38 rows=1,438 width=12) (actual time=2.191..2.192 rows=1,438 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 78kB
52. 1.178 1.178 ↑ 1.0 1,438 1

Seq Scan on claim_batch (cost=0.00..87.38 rows=1,438 width=12) (actual time=0.008..1.178 rows=1,438 loops=1)

53. 1.003 2.084 ↑ 1.0 1,438 1

Hash (cost=87.38..87.38 rows=1,438 width=12) (actual time=2.082..2.084 rows=1,438 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 78kB
54. 1.081 1.081 ↑ 1.0 1,438 1

Seq Scan on claim_batch claim_batch_1 (cost=0.00..87.38 rows=1,438 width=12) (actual time=0.004..1.081 rows=1,438 loops=1)

Planning time : 5.839 ms
Execution time : 24,782.294 ms