explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OaRH

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 1,275.840 ↑ 312.0 3 1

Sort (cost=273,646.73..273,649.07 rows=936 width=400) (actual time=1,275.840..1,275.840 rows=3 loops=1)

  • Sort Key: bill_charge.bill_no DESC
  • Sort Method: quicksort Memory: 25kB
2. 0.030 1,275.827 ↑ 312.0 3 1

HashAggregate (cost=273,591.18..273,600.54 rows=936 width=400) (actual time=1,275.822..1,275.827 rows=3 loops=1)

  • Group Key: bill_charge.bill_no, insurance_payment_allocation.payment_reference, ir.remittance_id, ir.received_date, ir.is_recovery, ir.reference_no
3. 0.062 1,275.797 ↑ 156.0 6 1

Append (cost=1.42..273,577.14 rows=936 width=400) (actual time=0.074..1,275.797 rows=6 loops=1)

4. 0.007 0.211 ↑ 12.3 6 1

Nested Loop (cost=1.42..2,219.64 rows=74 width=43) (actual time=0.074..0.211 rows=6 loops=1)

5. 0.011 0.174 ↑ 12.3 6 1

Nested Loop (cost=1.12..2,196.65 rows=74 width=27) (actual time=0.063..0.174 rows=6 loops=1)

6. 0.043 0.043 ↑ 21.8 5 1

Index Scan using bill_charge_bill_no_index on bill_charge (cost=0.56..442.45 rows=109 width=24) (actual time=0.038..0.043 rows=5 loops=1)

  • Index Cond: ((bill_no)::text = 'BL021604002893'::text)
7. 0.120 0.120 ↑ 3.0 1 5

Index Scan using idx_insurance_payment_allocation_charge_id on insurance_payment_allocation (cost=0.56..16.06 rows=3 width=23) (actual time=0.022..0.024 rows=1 loops=5)

  • Index Cond: ((charge_id)::text = (bill_charge.charge_id)::text)
8. 0.030 0.030 ↑ 1.0 1 6

Index Scan using insurance_remittance_pkey on insurance_remittance ir (cost=0.29..0.31 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (remittance_id = insurance_payment_allocation.remittance_id)
9. 0.000 1,275.524 ↓ 0.0 0 1

Nested Loop (cost=270,496.80..270,522.56 rows=1 width=43) (actual time=1,275.524..1,275.524 rows=0 loops=1)

  • Join Filter: (ipu.remittance_id = ir_1.remittance_id)
10. 0.006 1,275.524 ↓ 0.0 0 1

Merge Join (cost=270,496.50..270,522.24 rows=1 width=37) (actual time=1,275.524..1,275.524 rows=0 loops=1)

  • Merge Cond: (ipa.remittance_id = ipu.remittance_id)
11. 0.013 0.116 ↑ 12.3 6 1

Sort (cost=2,198.95..2,199.13 rows=74 width=27) (actual time=0.114..0.116 rows=6 loops=1)

  • Sort Key: ipa.remittance_id
  • Sort Method: quicksort Memory: 25kB
12. 0.005 0.103 ↑ 12.3 6 1

Nested Loop (cost=1.12..2,196.65 rows=74 width=27) (actual time=0.036..0.103 rows=6 loops=1)

13. 0.023 0.023 ↑ 21.8 5 1

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..442.45 rows=109 width=24) (actual time=0.020..0.023 rows=5 loops=1)

  • Index Cond: ((bill_no)::text = 'BL021604002893'::text)
14. 0.075 0.075 ↑ 3.0 1 5

Index Scan using idx_insurance_payment_allocation_charge_id on insurance_payment_allocation ipa (cost=0.56..16.06 rows=3 width=23) (actual time=0.014..0.015 rows=1 loops=5)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
15. 1,275.402 1,275.402 ↑ 151.0 1 1

Index Scan using idx_insurance_payment_unalloc_amount_remittance_id on insurance_payment_unalloc_amount ipu (cost=268,297.56..268,322.53 rows=151 width=10) (actual time=1,275.402..1,275.402 rows=1 loops=1)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 301
16. 53.843 1,245.443 ↑ 1.7 155,667 1

Nested Loop (cost=1.00..267,647.56 rows=259,940 width=4) (actual time=0.084..1,245.443 rows=155,667 loops=1)

17. 19.249 101.873 ↑ 1.7 155,667 1

Nested Loop (cost=0.43..35,621.65 rows=259,940 width=14) (actual time=0.054..101.873 rows=155,667 loops=1)

18. 0.178 0.178 ↑ 1.0 302 1

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.02 rows=302 width=4) (actual time=0.020..0.178 rows=302 loops=1)

19. 82.446 82.446 ↑ 1.8 515 302

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation insurance_payment_allocation_1 (cost=0.43..108.75 rows=918 width=14) (actual time=0.007..0.273 rows=515 loops=302)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
20. 1,089.669 1,089.669 ↑ 1.0 1 155,667

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.89 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=155,667)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 155667
21. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.29..0.31 rows=1 width=14) (never executed)

  • Index Cond: (remittance_id = ipa.remittance_id)
22. 0.000 0.058 ↓ 0.0 0 1

Nested Loop (cost=1.29..825.58 rows=861 width=42) (actual time=0.058..0.058 rows=0 loops=1)

23. 0.001 0.058 ↓ 0.0 0 1

Nested Loop (cost=0.73..57.04 rows=861 width=52) (actual time=0.058..0.058 rows=0 loops=1)

  • Join Filter: (ipu_1.remittance_id = ipa_1.remittance_id)
24. 0.000 0.057 ↓ 0.0 0 1

Nested Loop (cost=0.29..17.09 rows=1 width=46) (actual time=0.057..0.057 rows=0 loops=1)

25. 0.057 0.057 ↓ 0.0 0 1

Seq Scan on insurance_payment_unalloc_amount ipu_1 (cost=0.00..8.78 rows=1 width=32) (actual time=0.057..0.057 rows=0 loops=1)

  • Filter: ((bill_no)::text = 'BL021604002893'::text)
  • Rows Removed by Filter: 302
26. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_remittance_pkey on insurance_remittance ir_2 (cost=0.29..8.31 rows=1 width=14) (never executed)

  • Index Cond: (remittance_id = ipu_1.remittance_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using insurance_payment_allocation_remittance_id_index on insurance_payment_allocation ipa_1 (cost=0.43..28.48 rows=918 width=14) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using bill_charge_pkey on bill_charge bc_1 (cost=0.56..0.89 rows=1 width=10) (never executed)

  • Index Cond: (charge_id = (ipa_1.charge_id)::text)
  • Heap Fetches: 0