explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w62V

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

HashAggregate (cost=580,523.59..580,532.95 rows=936 width=400) (actual time=2,226.349..2,226.355 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
2. 0.000 2,226.324 ↑ 156.0 6 1

Append (cost=1.42..580,509.55 rows=936 width=400) (actual time=0.081..2,226.324 rows=6 loops=1)

3. 0.009 0.207 ↑ 12.3 6 1

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

4. 0.011 0.162 ↑ 12.3 6 1

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

5. 0.046 0.046 ↑ 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.040..0.046 rows=5 loops=1)

  • Index Cond: ((bill_no)::text = 'BL021604002893'::text)
6. 0.105 0.105 ↑ 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.019..0.021 rows=1 loops=5)

  • Index Cond: ((charge_id)::text = (bill_charge.charge_id)::text)
7. 0.036 0.036 ↑ 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.006..0.006 rows=1 loops=6)

  • Index Cond: (remittance_id = insurance_payment_allocation.remittance_id)
8. 49.744 2,263.638 ↓ 0.0 0 1

Gather (cost=269,753.22..577,454.97 rows=1 width=43) (actual time=2,226.076..2,263.638 rows=0 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 0.289 2,213.894 ↓ 0.0 0 3

Hash Join (cost=268,753.22..576,454.87 rows=1 width=43) (actual time=2,213.894..2,213.894 rows=0 loops=3)

  • Hash Cond: (ipa.remittance_id = ipu.remittance_id)
10. 0.024 2,213.605 ↑ 7.5 2 3

Nested Loop (cost=268,741.43..576,439.47 rows=15 width=41) (actual time=1,476.421..2,213.605 rows=2 loops=3)

11. 329.112 2,213.581 ↑ 7.5 2 3

Hash Join (cost=268,741.13..576,434.79 rows=15 width=27) (actual time=1,476.402..2,213.581 rows=2 loops=3)

  • Hash Cond: ((ipa.charge_id)::text = (bc.charge_id)::text)
12. 880.345 1,884.469 ↓ 1.6 4,049,873 3

Parallel Seq Scan on insurance_payment_allocation ipa (cost=268,297.32..569,345.70 rows=2,531,535 width=23) (actual time=1,035.337..1,884.469 rows=4,049,873 loops=3)

  • Filter: (NOT (hashed SubPlan 1))
  • Rows Removed by Filter: 583
13.          

SubPlan (forParallel Seq Scan)

14. 926.586 1,004.124 ↑ 1.7 155,667 3

Nested Loop (cost=1.00..267,647.47 rows=259,940 width=4) (actual time=0.116..1,004.124 rows=155,667 loops=3)

15. 77.243 77.538 ↑ 1.7 155,667 3

Nested Loop (cost=0.43..35,621.65 rows=259,940 width=14) (actual time=0.063..77.538 rows=155,667 loops=3)

16. 0.091 0.091 ↑ 1.0 302 3

Seq Scan on insurance_payment_unalloc_amount ipu_2 (cost=0.00..8.02 rows=302 width=4) (actual time=0.010..0.091 rows=302 loops=3)

17. 0.204 0.204 ↑ 1.8 515 906

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.005..0.204 rows=515 loops=906)

  • Index Cond: (remittance_id = ipu_2.remittance_id)
18. 2,800.869 2,802.006 ↑ 1.0 1 467,001

Index Only Scan using bill_charge_pkey on bill_charge bc_2 (cost=0.56..0.89 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=467,001)

  • Index Cond: (charge_id = (insurance_payment_allocation_1.charge_id)::text)
  • Heap Fetches: 155667
19. 0.018 0.339 ↑ 21.8 5 3

Hash (cost=442.45..442.45 rows=109 width=24) (actual time=0.113..0.113 rows=5 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.321 0.321 ↑ 21.8 5 3

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..442.45 rows=109 width=24) (actual time=0.096..0.107 rows=5 loops=3)

  • Index Cond: ((bill_no)::text = 'BL021604002893'::text)
21. 0.060 0.060 ↑ 1.0 1 6

Index Scan using insurance_remittance_pkey on insurance_remittance ir_1 (cost=0.29..0.31 rows=1 width=14) (actual time=0.010..0.010 rows=1 loops=6)

  • Index Cond: (remittance_id = ipa.remittance_id)
22. 0.255 0.699 ↑ 1.0 302 3

Hash (cost=8.02..8.02 rows=302 width=10) (actual time=0.233..0.233 rows=302 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
23. 0.444 0.444 ↑ 1.0 302 3

Seq Scan on insurance_payment_unalloc_amount ipu (cost=0.00..8.02 rows=302 width=10) (actual time=0.035..0.148 rows=302 loops=3)

24. 0.001 0.039 ↓ 0.0 0 1

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

25. 0.001 0.038 ↓ 0.0 0 1

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

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

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

27. 0.037 0.037 ↓ 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.037..0.037 rows=0 loops=1)

  • Filter: ((bill_no)::text = 'BL021604002893'::text)
  • Rows Removed by Filter: 302
28. 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)
29. 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)

  • Index Cond: (remittance_id = ir_2.remittance_id)
30. 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