explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IO5h

Settings
# exclusive inclusive rows x rows loops node
1. 0.194 39,895.338 ↑ 97.4 96 1

Nested Loop Left Join (cost=1,498,283.66..1,503,333.36 rows=9,354 width=727) (actual time=37,825.656..39,895.338 rows=96 loops=1)

  • rows=582424 loops=1)
  • Filter: ((is_resubmission = 'N'::bpchar) AND ((created_date)::date >= '20
  • Hash Cond: ((b.visit_id)::text = (pr_1.patient_id)::text)
  • Planning time: 10.449 ms
  • Execution time: 39999.524 ms
2. 496.968 39,895.048 ↑ 97.4 96 1

Merge Left Join (cost=1,498,283.24..1,498,331.02 rows=9,354 width=630) (actual time=37,825.639..39,895.048 rows=96 loops=1)

  • Merge Cond: ((icl.claim_id)::text = (cbt.claim_id)::text)
3. 0.228 488.746 ↑ 97.4 96 1

Sort (cost=89,000.39..89,023.77 rows=9,354 width=566) (actual time=488.726..488.746 rows=96 loops=1)

  • Sort Key: icl.claim_id
  • Sort Method: quicksort Memory: 49kB
4. 28.110 488.518 ↑ 97.4 96 1

Hash Right Join (cost=82,395.78..88,383.43 rows=9,354 width=566) (actual time=355.537..488.518 rows=96 loops=1)

  • Hash Cond: ((insurance_claim_resubmission.claim_id)::text = (icl.claim_id)::text)
5. 441.639 441.639 ↓ 1.5 371,355 1

HashAggregate (cost=33,847.33..36,365.36 rows=251,803 width=46) (actual time=331.175..441.639 rows=371,355 loops=1)

  • Group Key: insurance_claim_resubmission.claim_id
  • -> Seq Scan on insurance_claim_resubmission (cost=0.00..30935.22 rows=582422 width=23) (actual time=0.007..77.687
6. 0.000 18.769 ↑ 97.4 96 1

Hash (cost=48,431.53..48,431.53 rows=9,354 width=534) (actual time=18.769..18.769 rows=96 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 144kB
7. 6.813 18.810 ↑ 97.4 96 1

Gather (cost=1,021.95..48,431.53 rows=9,354 width=534) (actual time=17.370..18.810 rows=96 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
8. 11.764 11.997 ↑ 114.6 48 2

Hash Left Join (cost=21.95..46,496.13 rows=5,502 width=534) (actual time=10.930..11.997 rows=48 loops=2)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
  • -> Nested Loop (cost=20.48..46477.30 rows=5502 width=425) (actual time=10.899..11.953 rows=48 loops=2
  • -> Hash Left Join (cost=20.05..43685.22 rows=5502 width=407) (actual time=10.865..11.530 rows=4
  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
  • -> Nested Loop (cost=0.86..43651.46 rows=5502 width=395) (actual time=10.589..11.238 rows
  • -> Nested Loop (cost=0.43..40101.27 rows=5502 width=370) (actual time=10.555..10.83
  • -> Parallel Seq Scan on insurance_submission_batch firstsub (cost=0.00..3502.
  • Rows Removed by Filter: 36485
9. 0.005 0.005 ↑ 225.0 1 89

Index Scan using insurance_claim_submission_batch_id_index on insurance_claim icl (cost=0.43..232.36 rows=225 width=360) (actual time=0.005..0.005 rows=1 loops=89)

  • Index Cond: ((submission_batch_id)::text = (firstsub.submission_batch_id)::text)
10. 0.008 0.008 ↑ 1.0 1 96

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..0.65 rows=1 width=41) (actual time=0.008..0.008 rows=1 loops=96)

  • Index Cond: ((patient_id)::text = (icl.patient_id)::text)
11. 0.094 0.193 ↑ 1.0 453 2

Hash (cost=13.53..13.53 rows=453 width=32) (actual time=0.193..0.193 rows=453 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 37kB
12. 0.099 0.099 ↑ 1.0 453 2

Seq Scan on tpa_master ptpa (cost=0.00..13.53 rows=453 width=32) (actual time=0.016..0.099 rows=453 loops=2)

13. 0.008 0.008 ↑ 1.0 1 96

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.51 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=96)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
14. 0.007 0.019 ↑ 1.0 21 2

Hash (cost=1.21..1.21 rows=21 width=156) (actual time=0.019..0.019 rows=21 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.012 0.012 ↑ 1.0 21 2

Seq Scan on salutation_master sm (cost=0.00..1.21 rows=21 width=156) (actual time=0.010..0.012 rows=21 loops=2)

16. 14,461.467 38,909.334 ↓ 14,078.6 2,815,729 1

Sort (cost=1,409,282.85..1,409,283.35 rows=200 width=78) (actual time=37,330.055..38,909.334 rows=2,815,729 loops=1)

  • Sort Key: cbt.claim_id
  • Sort Method: external merge Disk: 99976kB
17. 262.742 24,447.867 ↓ 14,214.2 2,842,841 1

Subquery Scan on cbt (cost=1,409,270.21..1,409,275.21 rows=200 width=78) (actual time=21,983.869..24,447.867 rows=2,842,841 loops=1)

18. 5,405.292 24,185.125 ↓ 14,214.2 2,842,841 1

HashAggregate (cost=1,409,270.21..1,409,273.21 rows=200 width=302) (actual time=21,983.868..24,185.125 rows=2,842,841 loops=1)

  • Group Key: ctv.claim_id
19. 2,144.637 18,779.833 ↓ 1.4 2,884,035 1

Hash Join (cost=1,038,677.02..1,362,018.92 rows=2,100,057 width=94) (actual time=10,802.503..18,779.833 rows=2,884,035 loops=1)

20. 2,374.766 15,255.804 ↓ 1.4 2,884,035 1

Hash Join (cost=801,447.12..1,033,311.37 rows=2,100,057 width=109) (actual time=9,416.784..15,255.804 rows=2,884,035 loops=1)

  • Hash Cond: ((ctv.bill_no)::text = (b.bill_no)::text)
21. 251.250 9,344.678 ↓ 1.1 2,973,216 1

Subquery Scan on ctv (cost=525,853.42..639,348.48 rows=2,755,877 width=97) (actual time=5,874.340..9,344.678 rows=2,973,216 loops=1)

22. 2,034.041 9,093.428 ↓ 1.1 2,973,216 1

GroupAggregate (cost=525,853.42..611,789.71 rows=2,755,877 width=97) (actual time=5,874.339..9,093.428 rows=2,973,216 loops=1)

  • Group Key: bcl.claim_id, bcl.bill_no, bcl.priority
23. 6,484.261 7,059.387 ↓ 1.0 2,973,234 1

Sort (cost=525,853.42..533,286.44 rows=2,973,209 width=43) (actual time=5,874.324..7,059.387 rows=2,973,234 loops=1)

  • Sort Key: bcl.claim_id, bcl.bill_no, bcl.priority
  • Sort Method: external merge Disk: 162400kB
24. 575.126 575.126 ↓ 1.0 2,973,234 1

Seq Scan on bill_claim bcl (cost=0.00..114,718.09 rows=2,973,209 width=43) (actual time=0.008..575.126 rows=2,973,234 loops=1)

  • Filter: (claim_id IS NOT NULL)
25. 750.743 3,536.360 ↑ 1.0 2,842,143 1

Hash (cost=215,007.39..215,007.39 rows=2,845,865 width=41) (actual time=3,536.360..3,536.360 rows=2,842,143 loops=1)

  • Buckets: 2097152 Batches: 4 Memory Usage: 68500kB
26. 2,785.617 2,785.617 ↑ 1.0 2,842,143 1

Seq Scan on bill b (cost=0.00..215,007.39 rows=2,845,865 width=41) (actual time=0.028..2,785.617 rows=2,842,143 loops=1)

  • Filter: (is_tpa AND (status <> 'X'::bpchar))
  • Rows Removed by Filter: 890133
27. 682.811 1,379.392 ↑ 1.0 3,324,671 1

Hash (cost=179,435.51..179,435.51 rows=3,324,751 width=16) (actual time=1,379.392..1,379.392 rows=3,324,671 loops=1)

  • Buckets: 2097152 Batches: 2 Memory Usage: 94357kB
28. 696.581 696.581 ↑ 1.0 3,324,671 1

Seq Scan on patient_registration pr_1 (cost=0.00..179,435.51 rows=3,324,751 width=16) (actual time=0.040..696.581 rows=3,324,671 loops=1)

29. 0.096 0.096 ↓ 0.0 0 96

Index Scan using insurance_submission_batch_pkey on insurance_submission_batch resub (cost=0.42..0.48 rows=1 width=161) (actual time=0.001..0.001 rows=0 loops=96)