explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7bX

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 251,310.448 ↓ 1.0 40,503 1

Sort (cost=2,210,903.10..2,211,001.77 rows=39,467 width=739) (actual time=251,305.599..251,310.448 rows=40,503 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 14986kB
2. 267.940 251,140.593 ↓ 1.0 40,503 1

Hash Left Join (cost=1,993,717.90..2,207,890.12 rows=39,467 width=739) (actual time=245,395.080..251,140.593 rows=40,503 loops=1)

  • Hash Cond: ((max((insurance_claim_resubmission.resubmission_batch_id)::text)) = (resub.submission_batch_id)::text)
3. 946.694 250,872.653 ↓ 1.0 40,503 1

Hash Left Join (cost=1,984,651.27..2,196,549.20 rows=39,467 width=529) (actual time=245,184.013..250,872.653 rows=40,503 loops=1)

  • Hash Cond: ((icl.claim_id)::text = (insurance_claim_resubmission.claim_id)::text)
4. 268.412 249,925.959 ↓ 1.0 40,503 1

Hash Left Join (cost=1,942,561.44..2,154,355.76 rows=39,467 width=497) (actual time=244,266.099..249,925.959 rows=40,503 loops=1)

  • Hash Cond: ((icl.claim_id)::text = (cbt.claim_id)::text)
5. 0.000 6,120.145 ↓ 1.0 40,503 1

Gather (cost=6,964.49..218,655.22 rows=39,467 width=433) (actual time=728.688..6,120.145 rows=40,503 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 9,551.695 9,598.464 ↑ 1.2 13,501 3

Hash Left Join (cost=5,964.49..213,708.52 rows=16,445 width=433) (actual time=684.759..9,598.464 rows=13,501 loops=3)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Nested Loop (cost=5963.02..213655.17 rows=16445 width=437) (actual time=684.722..9586.512 rows=13501 loops=
  • -> Hash Left Join (cost=5962.59..204258.10 rows=16445 width=419) (actual time=684.485..7731.311 rows=135
  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
  • -> Hash Join (cost=5943.40..204195.37 rows=16445 width=407) (actual time=683.953..7719.932 rows=13
  • Hash Cond: ((icl.submission_batch_id)::text = (firstsub.submission_batch_id)::text)
  • -> Nested Loop (cost=3.73..198102.76 rows=58257 width=397) (actual time=3.009..7639.249 rows
  • -> Hash Join (cost=3.30..164628.58 rows=60044 width=52) (actual time=2.605..2261.763 r
  • Hash Cond: (pr.center_id = hcm.center_id)
  • -> Parallel Seq Scan on patient_registration pr (cost=0.00..160332.13 rows=13810
  • -> Hash (cost=3.29..3.29 rows=1 width=15) (actual time=0.140..0.140 rows=1 loops
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • -> Seq Scan on hospital_center_master hcm (cost=0.00..3.29 rows=1 width=15
  • Filter: ((center_name)::text = 'Brightpoint'::text)
  • Rows Removed by Filter: 22
  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
7. 7.469 46.099 ↓ 1.1 27,283 3

Hash (cost=5,616.96..5,616.96 rows=25,817 width=19) (actual time=46.098..46.099 rows=27,283 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 1749kB
8. 38.630 38.630 ↓ 1.1 27,283 3

Index Scan using insurance_submission_batch_created_date_idx on insurance_submission_batch firstsub (cost=0.29..5,616.96 rows=25,817 width=19) (actual time=0.358..38.630 rows=27,283 loops=3)

  • Index Cond: (((created_date)::date >= '2017-11-01'::date) AND ((created_date)::date <= '2018-11-30'::date))
  • Filter: (is_resubmission = 'N'::bpchar)
  • Rows Removed by Filter: 8369
9. 0.123 0.511 ↑ 1.0 453 3

Hash (cost=13.53..13.53 rows=453 width=32) (actual time=0.511..0.511 rows=453 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
10. 0.388 0.388 ↑ 1.0 453 3

Seq Scan on tpa_master ptpa (cost=0.00..13.53 rows=453 width=32) (actual time=0.133..0.388 rows=453 loops=3)

11. 0.135 0.135 ↑ 1.0 1 40,503

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.57 rows=1 width=48) (actual time=0.135..0.135 rows=1 loops=40,503)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
12. 0.007 0.024 ↑ 1.0 21 3

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.024..0.024 rows=21 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.017 0.017 ↑ 1.0 21 3

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=14) (actual time=0.012..0.017 rows=21 loops=3)

14. 820.947 243,537.402 ↓ 2,797,328.0 2,797,328 1

Hash (cost=1,935,596.93..1,935,596.93 rows=1 width=78) (actual time=243,537.402..243,537.402 rows=2,797,328 loops=1)

  • Buckets: 2097152 (originally 1024) Batches: 2 (originally 1) Memory Usage: 95651kB
15. 334.814 242,716.455 ↓ 2,797,328.0 2,797,328 1

Subquery Scan on cbt (cost=1,935,588.78..1,935,596.93 rows=1 width=78) (actual time=175,796.293..242,716.455 rows=2,797,328 loops=1)

16. 3,929.034 242,381.641 ↓ 2,797,328.0 2,797,328 1

GroupAggregate (cost=1,935,588.78..1,935,596.92 rows=1 width=302) (actual time=175,796.292..242,381.641 rows=2,797,328 loops=1)

  • Group Key: bcl.claim_id
17. 17,874.508 238,452.607 ↓ 2,836,355.0 2,836,355 1

Nested Loop (cost=1,935,588.78..1,935,596.88 rows=1 width=94) (actual time=175,796.254..238,452.607 rows=2,836,355 loops=1)

18. 220,578.099 220,578.099 ↓ 2,836,355.0 2,836,355 1

Nested Loop (cost=1,935,588.35..1,935,596.42 rows=1 width=109) (actual time=175,795.198..220,578.099 rows=2,836,355 loops=1)

19. 0.000 189,815.957 ↓ 2,838,189.0 2,838,189 1

GroupAggregate (cost=1,935,587.92..1,935,587.96 rows=1 width=96) (actual time=175,794.218..189,815.957 rows=2,838,189 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
20. 48,392.004 184,190.954 ↓ 14,981,901.0 14,981,901 1

Sort (cost=1,935,587.92..1,935,587.93 rows=1 width=40) (actual time=175,794.201..184,190.954 rows=14,981,901 loops=1)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: external merge Disk: 791040kB
21. 1,898.580 135,798.950 ↓ 14,981,901.0 14,981,901 1

Gather (cost=1,784,435.62..1,935,587.91 rows=1 width=40) (actual time=42,911.005..135,798.950 rows=14,981,901 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 77,535.987 133,900.370 ↓ 4,993,967.0 4,993,967 3

Nested Loop (cost=1,783,435.62..1,934,587.81 rows=1 width=40) (actual time=43,863.523..133,900.370 rows=4,993,967 loops=3)

23. 3,742.506 56,364.369 ↓ 5,287,408.0 5,287,408 3

Merge Join (cost=1,783,435.06..1,934,586.97 rows=1 width=65) (actual time=43,863.152..56,364.369 rows=5,287,408 loops=3)

  • Merge Cond: ((("substring"((bcc.bill_no)::text, 5, 2)) = ("substring"((bcl.bill_no)::text, 5, 2))) AND ((bcc.bill_no)::text = (bcl.bill_no)::text) AND ((bcc.claim_id)::text = (bcl.claim_id)::text))
24. 28,277.439 33,544.936 ↑ 1.2 5,287,410 3

Sort (cost=1,326,337.74..1,342,860.79 rows=6,609,220 width=47) (actual time=27,894.755..33,544.936 rows=5,287,410 loops=3)

  • Sort Key: ("substring"((bcc.bill_no)::text, 5, 2)), bcc.bill_no, bcc.claim_id
  • Sort Method: external merge Disk: 318944kB
25. 5,267.497 5,267.497 ↑ 1.2 5,287,410 3

Parallel Seq Scan on bill_charge_claim bcc (cost=0.00..374,332.20 rows=6,609,220 width=47) (actual time=0.136..5,267.497 rows=5,287,410 loops=3)

26. 584.980 19,076.927 ↓ 2.3 6,778,503 3

Materialize (cost=457,097.31..471,958.34 rows=2,972,206 width=32) (actual time=15,968.387..19,076.927 rows=6,778,503 loops=3)

27. 17,594.323 18,491.947 ↓ 1.0 2,973,189 3

Sort (cost=457,097.31..464,527.83 rows=2,972,206 width=32) (actual time=15,968.383..18,491.947 rows=2,973,189 loops=3)

  • Sort Key: ("substring"((bcl.bill_no)::text, 5, 2)), bcl.bill_no, bcl.claim_id
  • Sort Method: external merge Disk: 141336kB
28. 897.624 897.624 ↓ 1.0 2,973,190 3

Seq Scan on bill_claim bcl (cost=0.00..66,426.06 rows=2,972,206 width=32) (actual time=0.203..897.624 rows=2,973,190 loops=3)

29. 0.014 0.014 ↑ 1.0 1 15,862,223

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.56..0.83 rows=1 width=24) (actual time=0.014..0.014 rows=1 loops=15,862,223)

  • Index Cond: ((charge_id)::text = (bcc.charge_id)::text)
  • Filter: ((status <> 'X'::bpchar) AND ("substring"((bcc.bill_no)::text, 5, 2) = "substring"((bill_no)::text, 5, 2)))
  • Rows Removed by Filter: 0
30. 28,381.890 28,381.890 ↑ 1.0 1 2,838,189

Index Scan using bill_pkey on bill b (cost=0.43..8.45 rows=1 width=41) (actual time=0.010..0.010 rows=1 loops=2,838,189)

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 0
31. 17,018.130 17,018.130 ↑ 1.0 1 2,836,355

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.43..0.47 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=2,836,355)

  • Index Cond: (patient_id = (b.visit_id)::text)
  • Heap Fetches: 1539
32. 77.400 917.685 ↓ 1.5 371,353 1

Hash (cost=38,921.95..38,921.95 rows=253,431 width=46) (actual time=917.685..917.685 rows=371,353 loops=1)

  • Buckets: 524288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 24338kB
33. 443.337 840.285 ↓ 1.5 371,353 1

HashAggregate (cost=33,853.33..36,387.64 rows=253,431 width=46) (actual time=715.660..840.285 rows=371,353 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
34. 396.948 396.948 ↑ 1.0 582,422 1

Seq Scan on insurance_claim_resubmission (cost=0.00..30,941.22 rows=582,422 width=23) (actual time=0.464..396.948 rows=582,422 loops=1)

35. 25.252 210.954 ↑ 1.0 73,050 1

Hash (cost=8,153.50..8,153.50 rows=73,050 width=161) (actual time=210.954..210.954 rows=73,050 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 15042kB
36. 185.702 185.702 ↑ 1.0 73,050 1

Seq Scan on insurance_submission_batch resub (cost=0.00..8,153.50 rows=73,050 width=161) (actual time=0.455..185.702 rows=73,050 loops=1)