explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EJ93

Settings
# exclusive inclusive rows x rows loops node
1. 29.577 344,962.420 ↑ 1.0 1 1

Aggregate (cost=8,070,725.62..8,070,725.63 rows=1 width=0) (actual time=344,962.419..344,962.420 rows=1 loops=1)

2. 4,705.160 344,932.843 ↓ 76,341.3 458,048 1

Merge Join (cost=7,933,450.84..8,070,725.60 rows=6 width=0) (actual time=296,994.030..344,932.843 rows=458,048 loops=1)

  • Merge Cond: (((ish.claim_id)::text = (ipa.claim_id)::text) AND ((ish.activity_id)::text = (ipa.charge_id)::text))
3. 154,669.626 332,601.350 ↑ 1.0 18,044,564 1

Sort (cost=7,654,738.58..7,699,985.20 rows=18,098,650 width=25) (actual time=289,410.327..332,601.350 rows=18,044,564 loops=1)

  • Sort Key: ish.claim_id, ish.activity_id
  • Sort Method: external merge Disk: 644384kB
4. 1,832.838 177,931.724 ↑ 1.0 18,077,347 1

Subquery Scan on ish (cost=4,135,043.04..5,039,975.54 rows=18,098,650 width=25) (actual time=150,083.609..177,931.724 rows=18,077,347 loops=1)

5. 9,535.925 176,098.886 ↑ 1.0 18,077,347 1

Unique (cost=4,135,043.04..4,858,989.04 rows=18,098,650 width=147) (actual time=150,083.606..176,098.886 rows=18,077,347 loops=1)

6. 163,827.944 166,562.961 ↑ 1.0 18,098,650 1

Sort (cost=4,135,043.04..4,180,289.66 rows=18,098,650 width=147) (actual time=150,083.605..166,562.961 rows=18,098,650 loops=1)

  • Sort Key: insurance_submission_history.submission_batch_id, insurance_submission_history.claim_id, insurance_submission_history.activity_id, insurance_submission_history.item_code, insurance_submission_history.amount, insurance_submission_history.insurance_claim_amount, insurance_submission_history.charge_head, insurance_submission_history.act_description, insurance_submission_history.act_quantity, insurance_submission_history.clinician_id, insurance_submission_history.member_id, insurance_submission_history.insurance_co, insurance_submission_history.sponsor_id, insurance_submission_history.resubmission, insurance_submission_history.patient_share
  • Sort Method: external merge Disk: 2874712kB
7. 2,735.017 2,735.017 ↑ 1.0 18,098,650 1

Seq Scan on insurance_submission_history (cost=0.00..592,370.50 rows=18,098,650 width=147) (actual time=0.004..2,735.017 rows=18,098,650 loops=1)

8. 1,441.609 7,626.333 ↓ 2.6 530,495 1

Sort (cost=278,712.27..279,223.87 rows=204,643 width=24) (actual time=7,578.014..7,626.333 rows=530,495 loops=1)

  • Sort Key: ipa.claim_id, ipa.charge_id
  • Sort Method: quicksort Memory: 36603kB
9. 113.557 6,184.724 ↓ 1.6 317,270 1

Nested Loop (cost=0.44..260,659.94 rows=204,643 width=24) (actual time=394.895..6,184.724 rows=317,270 loops=1)

10. 498.079 498.079 ↓ 1.0 1,776 1

Seq Scan on insurance_remittance ir (cost=0.00..5,958.80 rows=1,758 width=4) (actual time=393.637..498.079 rows=1,776 loops=1)

  • Filter: ((received_date >= '2018-09-01'::date) AND (received_date <= '2018-09-10'::date))
  • Rows Removed by Filter: 163892
11. 5,573.088 5,573.088 ↑ 6.1 179 1,776

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa (cost=0.44..133.93 rows=1,095 width=28) (actual time=0.586..3.138 rows=179 loops=1,776)

  • Index Cond: (remittance_id = ir.remittance_id)