explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9opm

Settings
# exclusive inclusive rows x rows loops node
1. 2,785.980 71,296.940 ↑ 40.9 85,464 1

GroupAggregate (cost=2,420,987.53..2,525,735.17 rows=3,491,588 width=73) (actual time=62,309.537..71,296.940 rows=85,464 loops=1)

2. 55,046.026 68,510.960 ↓ 2.0 7,011,081 1

Sort (cost=2,420,987.53..2,429,716.50 rows=3,491,588 width=73) (actual time=62,309.448..68,510.960 rows=7,011,081 loops=1)

  • Sort Key: ipa.payment_reference, ipa.remittance_id, ir.center_id, (CASE WHEN ((tm.tpa_master_id IS NULL) OR ((tm.tpa_master_id)::text = ''::text)) THEN tm.tpa_id ELSE tm.tpa_master_id END), (sum(insurance_payment_allocation.amount))
  • Sort Method: external merge Disk: 380120kB
3. 2,126.119 13,464.934 ↓ 2.0 7,011,081 1

Hash Join (cost=661,687.75..1,908,550.32 rows=3,491,588 width=73) (actual time=6,233.185..13,464.934 rows=7,011,081 loops=1)

  • Hash Cond: (ipa.remittance_id = ir.remittance_id)
4. 614.607 11,249.421 ↓ 2.0 7,017,458 1

Nested Loop (cost=655,189.42..1,818,673.19 rows=3,531,906 width=50) (actual time=6,143.742..11,249.421 rows=7,017,458 loops=1)

5. 2,073.618 6,161.386 ↓ 8.6 27,277 1

HashAggregate (cost=655,188.86..655,220.58 rows=3,172 width=14) (actual time=6,143.710..6,161.386 rows=27,277 loops=1)

6. 1,412.875 4,087.768 ↑ 1.0 7,011,081 1

Hash Join (cost=4,989.29..620,041.83 rows=7,029,406 width=14) (actual time=50.035..4,087.768 rows=7,011,081 loops=1)

  • Hash Cond: (insurance_payment_allocation.remittance_id = ir_1.remittance_id)
7. 2,624.944 2,624.944 ↓ 1.0 7,125,571 1

Seq Scan on insurance_payment_allocation (cost=0.00..464,764.49 rows=7,110,577 width=18) (actual time=0.018..2,624.944 rows=7,125,571 loops=1)

  • Filter: (amount <> 0::numeric)
  • Rows Removed by Filter: 7207318
8. 17.370 49.949 ↑ 1.0 107,436 1

Hash (cost=3,645.90..3,645.90 rows=107,471 width=4) (actual time=49.949..49.949 rows=107,436 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 3778kB
9. 32.579 32.579 ↑ 1.0 107,436 1

Seq Scan on insurance_remittance ir_1 (cost=0.00..3,645.90 rows=107,471 width=4) (actual time=0.003..32.579 rows=107,436 loops=1)

  • Filter: (is_payment_matched = 'N'::bpchar)
  • Rows Removed by Filter: 1276
10. 4,473.428 4,473.428 ↑ 4.3 257 27,277

Index Scan using insurance_payment_allocation_payment_reference_index on insurance_payment_allocation ipa (cost=0.56..355.65 rows=1,113 width=18) (actual time=0.013..0.164 rows=257 loops=27,277)

  • Index Cond: ((payment_reference)::text = (insurance_payment_allocation.payment_reference)::text)
  • Filter: (amount <> 0::numeric)
  • Rows Removed by Filter: 243
11. 24.643 89.394 ↑ 1.0 107,436 1

Hash (cost=5,154.94..5,154.94 rows=107,471 width=27) (actual time=89.394..89.394 rows=107,436 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 5439kB
12. 64.108 64.751 ↑ 1.0 107,436 1

Hash Join (cost=31.32..5,154.94 rows=107,471 width=27) (actual time=0.660..64.751 rows=107,436 loops=1)

  • Hash Cond: ((ir.tpa_id)::text = (tm.tpa_id)::text)
  • -> Seq Scan on insurance_remittance ir (cost=0.00..3645.90 rows=107471 width=18) (actual time=0.008..33.887 rows=107
  • Filter: (is_payment_matched = 'N'::bpchar)
  • Rows Removed by Filter: 1276
13. 0.222 0.643 ↑ 1.0 503 1

Hash (cost=25.03..25.03 rows=503 width=19) (actual time=0.643..0.643 rows=503 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
14. 0.421 0.421 ↑ 1.0 503 1

Seq Scan on tpa_master tm (cost=0.00..25.03 rows=503 width=19) (actual time=0.004..0.421 rows=503 loops=1)