explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AYiGW

Settings
# exclusive inclusive rows x rows loops node
1. 5,897.448 169,878.833 ↓ 1,676,902.0 1,676,902 1

Nested Loop (cost=5,937,889.54..6,018,136.45 rows=1 width=509) (actual time=99,559.461..169,878.833 rows=1,676,902 loops=1)

2. 0.000 122,058.835 ↓ 1,676,902.0 1,676,902 1

Gather (cost=5,937,889.11..6,018,132.52 rows=1 width=183) (actual time=99,557.567..122,058.835 rows=1,676,902 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 24,771.494 133,589.733 ↓ 558,967.0 558,967 3

Nested Loop (cost=5,936,889.11..6,017,132.42 rows=1 width=183) (actual time=99,497.064..133,589.733 rows=558,967 loops=3)

4. 3,241.362 108,818.204 ↓ 697,273.0 697,273 3

Merge Join (cost=5,936,888.55..6,017,128.36 rows=1 width=167) (actual time=99,496.559..108,818.204 rows=697,273 loops=3)

  • Merge Cond: (((bc.bill_no)::text = (bill_claim.bill_no)::text) AND ((bcc.claim_id)::text = (bill_claim.claim_id)::text))
5. 3,640.230 70,697.760 ↑ 3.9 697,292 3

Sort (cost=4,906,304.48..4,913,147.85 rows=2,737,350 width=162) (actual time=70,178.953..70,697.760 rows=697,292 loops=3)

  • Sort Key: bc.bill_no, bcc.claim_id
  • Sort Method: external merge Disk: 123152kB
6. 13,928.556 67,057.530 ↑ 3.9 697,292 3

Hash Join (cost=1,523,300.69..4,421,151.24 rows=2,737,350 width=162) (actual time=20,581.372..67,057.530 rows=697,292 loops=3)

  • Hash Cond: ((bc.charge_id)::text = (bcc.charge_id)::text)
7. 32,588.214 32,588.214 ↑ 1.2 12,574,612 3

Parallel Seq Scan on bill_charge bc (cost=0.00..2,077,366.50 rows=15,708,050 width=118) (actual time=0.211..32,588.214 rows=12,574,612 loops=3)

8. 807.077 20,540.760 ↑ 3.1 2,091,877 3

Hash (cost=1,377,023.19..1,377,023.19 rows=6,569,640 width=54) (actual time=20,540.760..20,540.760 rows=2,091,877 loops=3)

  • Buckets: 2097152 Batches: 8 Memory Usage: 39742kB
9. 6,615.827 19,733.683 ↑ 3.1 2,091,877 3

Hash Join (cost=262,610.75..1,377,023.19 rows=6,569,640 width=54) (actual time=3,607.844..19,733.683 rows=2,091,877 loops=3)

  • Hash Cond: ((bcc.claim_id)::text = (ic.claim_id)::text)
10. 9,527.589 9,527.589 ↓ 1.0 33,365,569 3

Seq Scan on bill_charge_claim bcc (cost=0.00..1,026,897.40 rows=33,339,040 width=36) (actual time=0.214..9,527.589 rows=33,365,569 loops=3)

11. 457.861 3,590.267 ↓ 1.0 1,351,904 3

Hash (cost=245,730.49..245,730.49 rows=1,350,421 width=18) (actual time=3,590.267..3,590.267 rows=1,351,904 loops=3)

  • Buckets: 2097152 Batches: 1 Memory Usage: 80842kB
12. 3,132.406 3,132.406 ↓ 1.0 1,351,904 3

Seq Scan on insurance_claim ic (cost=0.00..245,730.49 rows=1,350,421 width=18) (actual time=0.187..3,132.406 rows=1,351,904 loops=3)

  • Filter: (status = 'O'::bpchar)
  • Rows Removed by Filter: 5501098
13. 743.619 34,879.082 ↓ 1.1 6,577,123 3

Materialize (cost=1,030,204.43..1,060,586.26 rows=6,076,366 width=33) (actual time=29,317.560..34,879.082 rows=6,577,123 loops=3)

14. 30,689.164 34,135.463 ↓ 1.0 6,093,092 3

Sort (cost=1,030,204.43..1,045,395.35 rows=6,076,366 width=33) (actual time=29,317.554..34,135.463 rows=6,093,092 loops=3)

  • Sort Key: bill_claim.bill_no, bill_claim.claim_id
  • Sort Method: external merge Disk: 261256kB
15. 3,446.299 3,446.299 ↓ 1.0 6,093,095 3

Seq Scan on bill_claim (cost=0.00..203,140.66 rows=6,076,366 width=33) (actual time=0.189..3,446.299 rows=6,093,095 loops=3)

16. 0.035 0.035 ↑ 1.0 1 2,091,820

Index Scan using bill_pkey on bill b (cost=0.56..4.05 rows=1 width=45) (actual time=0.035..0.035 rows=1 loops=2,091,820)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: (status = ANY ('{F,C}'::bpchar[]))
  • Rows Removed by Filter: 0
17. 41,922.550 41,922.550 ↑ 1.0 1 1,676,902

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..3.88 rows=1 width=131) (actual time=0.025..0.025 rows=1 loops=1,676,902)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
Planning time : 129.636 ms
Execution time : 170,023.533 ms