explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bt7U

Settings
# exclusive inclusive rows x rows loops node
1. 0.028 22.754 ↓ 1.2 5 1

Sort (cost=70.60..70.61 rows=4 width=570) (actual time=22.753..22.754 rows=5 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 26kB
  • (cost=0.15..20.71 rows=285 width=228) (actual time=0.021..0.045 rows=36 loops=1)
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Rows Removed by Filter: 0
  • Planning time: 11.805 ms
  • Execution time: 23.746 ms
2. 0.053 22.726 ↓ 1.2 5 1

Nested Loop Left Join (cost=58.66..70.56 rows=4 width=570) (actual time=22.591..22.726 rows=5 loops=1)

3. 0.012 22.673 ↓ 1.2 5 1

Nested Loop Left Join (cost=58.52..69.44 rows=4 width=491) (actual time=22.556..22.673 rows=5 loops=1)

4. 0.016 22.641 ↓ 1.2 5 1

Nested Loop (cost=58.39..68.70 rows=4 width=382) (actual time=22.542..22.641 rows=5 loops=1)

5. 0.013 22.585 ↓ 1.2 5 1

Nested Loop Left Join (cost=58.11..66.69 rows=4 width=375) (actual time=22.521..22.585 rows=5 loops=1)

6. 0.013 22.547 ↓ 1.2 5 1

Nested Loop (cost=57.97..65.93 rows=4 width=360) (actual time=22.505..22.547 rows=5 loops=1)

7. 0.031 22.494 ↓ 1.2 5 1

Hash Right Join (cost=57.69..62.47 rows=4 width=334) (actual time=22.486..22.494 rows=5 loops=1)

  • Hash Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
8. 0.010 0.010 ↓ 0.0 0 1

HashAggregate (cost=14.35..16.35 rows=200 width=80) (actual time=0.010..0.010 rows=0 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..12.90 rows=290 width=96) (actual time=0
9. 0.013 22.453 ↓ 1.2 5 1

Hash (cost=43.29..43.29 rows=4 width=302) (actual time=22.453..22.453 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 2.185 22.440 ↓ 1.2 5 1

Merge Join (cost=38.38..43.29 rows=4 width=302) (actual time=22.224..22.440 rows=5 loops=1)

  • Merge Cond: ((icl.submission_batch_id)::text = (firstsub.submission_batch_id)::text)
  • -> Nested Loop Left Join (cost=36.64..70.50 rows=285 width=292) (actual time=17.000..22.3
  • Join Filter: ((cbt.claim_id)::text = (icl.claim_id)::text)
  • Rows Removed by Join Filter: 5972
  • -> Index Scan using insurance_claim_submission_batch_id_index on insurance_claim icl
  • -> Materialize (cost=36.50..45.51 rows=1 width=79) (actual time=0.471..0.591 rows=1
  • -> Subquery Scan on cbt (cost=36.50..45.51 rows=1 width=79) (actual time=16.9
  • Group Key: bcl.claim_id
11. 0.089 20.195 ↓ 174.0 174 1

Nested Loop (cost=36.50..45.46 rows=1 width=95) (actual time=16.859..20.195 rows=174 loops=1)

12. 0.260 18.888 ↓ 174.0 174 1

Nested Loop (cost=36.22..44.29 rows=1 width=110) (actual time=16.843..18.888 rows=174 loops=1)

13. 0.516 17.410 ↓ 174.0 174 1

GroupAggregate (cost=35.95..35.98 rows=1 width=97) (actual time=16.804..17.410 rows=174 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
14. 6.613 16.894 ↓ 560.0 560 1

Sort (cost=35.95..35.95 rows=1 width=40) (actual time=16.770..16.894 rows=560 loops=1)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: quicksort Memory: 68kB
15. 0.838 10.281 ↓ 560.0 560 1

Nested Loop (cost=8.53..35.94 rows=1 width=40) (actual time=0.518..10.281 rows=560 loops=1)

16. 1.970 2.590 ↓ 623.0 623 1

Hash Join (cost=8.25..35.03 rows=1 width=63) (actual time=0.470..2.590 rows=623 loops=1)

  • Hash 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))
17. 0.214 0.214 ↓ 1.0 623 1

Seq Scan on bill_charge_claim bcc (cost=0.00..18.22 rows=622 width=45) (actual time=0.023..0.214 rows=623 loops=1)

18. 0.281 0.406 ↓ 1.0 192 1

Hash (cost=4.91..4.91 rows=191 width=33) (actual time=0.406..0.406 rows=192 loops=1)

19. 0.125 0.125 ↓ 1.0 192 1

Seq Scan on bill_claim bcl (cost=0.00..4.91 rows=191 width=33) (actual time=0.017..0.125 rows=192 loops=1)

  • Filter: (claim_id IS NOT NULL)
20. 6.853 6.853 ↑ 1.0 1 623

Index Scan using bill_charge_pkey on bill_charge bc (cost=0.28..0.89 rows=1 width=23) (actual time=0.011..0.011 rows=1 loops=623)

  • 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)))
21. 1.218 1.218 ↑ 1.0 1 174

Index Scan using bill_pkey on bill b (cost=0.28..8.29 rows=1 width=41) (actual time=0.007..0.007 rows=1 loops=174)

  • Index Cond: ((bill_no)::text = (bcl.bill_no)::text)
  • Filter: (is_tpa AND (status <> 'X'::bpchar))
22. 1.218 1.218 ↑ 1.0 1 174

Index Only Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.28..1.16 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=174)

  • Index Cond: (patient_id = (b.visit_id)::text)
  • Heap Fetches: 174
23. 0.032 0.060 ↓ 1.2 5 1

Sort (cost=1.74..1.75 rows=4 width=19) (actual time=0.059..0.060 rows=5 loops=1)

  • Sort Key: firstsub.submission_batch_id
  • Sort Method: quicksort Memory: 25kB
24. 0.028 0.028 ↓ 1.2 5 1

Seq Scan on insurance_submission_batch firstsub (cost=0.00..1.70 rows=4 width=19) (actual time=0.021..0.028 rows=5 loops=1)

  • Filter: ((is_resubmission = 'N'::bpchar) AND ((created_date)::date >= '2018-10-01'::date) AND ((created_date)::date <= '2018-10-30'::date))
  • Rows Removed by Filter: 28
25. 0.040 0.040 ↑ 1.0 1 5

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.28..0.85 rows=1 width=42) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: ((patient_id)::text = (icl.patient_id)::text)
26. 0.025 0.025 ↑ 1.0 1 5

Index Scan using tpa_master_pkey on tpa_master ptpa (cost=0.14..0.18 rows=1 width=35) (actual time=0.004..0.005 rows=1 loops=5)

27. 0.040 0.040 ↑ 1.0 1 5

Index Scan using patient_details_pkey on patient_details pd (cost=0.28..0.49 rows=1 width=39) (actual time=0.008..0.008 rows=1 loops=5)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
28. 0.020 0.020 ↑ 1.0 1 5

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.18 rows=1 width=156) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: ((pd.salutation)::text = (salutation_id)::text)
29. 0.000 0.000 ↓ 0.0 0 5

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.14..0.21 rows=1 width=133) (actual time=0.000..0.000 rows=0 loops=5)

  • Index Cond: ((submission_batch_id)::text = (max((insurance_claim_resubmission.resubmission_batch_id)::text)))