explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Fod

Settings
# exclusive inclusive rows x rows loops node
1. 6,585.509 198,220.587 ↓ 1,641,307.0 1,641,307 1

Nested Loop (cost=5,558,849.63..5,629,168.66 rows=1 width=509) (actual time=106,109.293..198,220.587 rows=1,641,307 loops=1)

2. 0.000 129,265.412 ↓ 1,641,307.0 1,641,307 1

Gather (cost=5,558,849.20..5,629,164.72 rows=1 width=183) (actual time=106,107.821..129,265.412 rows=1,641,307 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 28,979.745 144,344.046 ↓ 547,102.0 547,102 3

Nested Loop (cost=5,557,849.20..5,628,164.62 rows=1 width=183) (actual time=106,119.448..144,344.046 rows=547,102 loops=3)

4. 3,193.335 115,364.259 ↓ 685,408.0 685,408 3

Merge Join (cost=5,557,848.64..5,628,160.57 rows=1 width=167) (actual time=106,118.138..115,364.259 rows=685,408 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,810.838 76,946.129 ↑ 2.0 685,427 3

Sort (cost=4,527,264.57..4,530,686.26 rows=1,368,676 width=162) (actual time=76,419.808..76,946.129 rows=685,427 loops=3)

  • Sort Key: bc.bill_no, bcc.claim_id
  • Sort Method: external merge Disk: 121888kB
6. 13,512.226 73,135.291 ↑ 2.0 685,427 3

Hash Join (cost=1,517,984.15..4,291,529.72 rows=1,368,676 width=162) (actual time=26,169.633..73,135.291 rows=685,427 loops=3)

  • Hash Cond: ((bc.charge_id)::text = (bcc.charge_id)::text)
7. 33,472.016 33,472.016 ↑ 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.345..33,472.016 rows=12,574,612 loops=3)

8. 814.424 26,151.049 ↑ 1.6 2,056,281 3

Hash (cost=1,444,844.86..1,444,844.86 rows=3,284,823 width=54) (actual time=26,151.049..26,151.049 rows=2,056,281 loops=3)

  • Buckets: 2097152 Batches: 4 Memory Usage: 62297kB
9. 6,837.993 25,336.625 ↑ 1.6 2,056,281 3

Hash Join (cost=330,432.42..1,444,844.86 rows=3,284,823 width=54) (actual time=9,273.336..25,336.625 rows=2,056,281 loops=3)

  • Hash Cond: ((bcc.claim_id)::text = (ic.claim_id)::text)
10. 9,233.662 9,233.662 ↓ 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.172..9,233.662 rows=33,365,569 loops=3)

11. 495.177 9,264.970 ↓ 2.0 1,350,748 3

Hash (cost=321,992.29..321,992.29 rows=675,211 width=18) (actual time=9,264.970..9,264.970 rows=1,350,748 loops=3)

  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 80787kB
12. 5,332.637 8,769.793 ↓ 2.0 1,350,748 3

Seq Scan on insurance_claim ic (cost=59,129.30..321,992.29 rows=675,211 width=18) (actual time=4,184.008..8,769.793 rows=1,350,748 loops=3)

  • Filter: ((NOT (hashed SubPlan 1)) AND (status = 'O'::bpchar))
  • Rows Removed by Filter: 5502254
13.          

SubPlan (forSeq Scan)

14. 3,437.156 3,437.156 ↓ 1.0 1,809,561 3

Index Only Scan using insurance_claim_resubmission_claim_id_index on insurance_claim_resubmission rsub (cost=0.43..54,610.18 rows=1,807,650 width=14) (actual time=2.106..3,437.156 rows=1,809,561 loops=3)

  • Heap Fetches: 288
15. 676.756 35,224.795 ↓ 1.1 6,566,222 3

Materialize (cost=1,030,204.43..1,060,586.26 rows=6,076,366 width=33) (actual time=29,698.285..35,224.795 rows=6,566,222 loops=3)

16. 30,901.750 34,548.039 ↓ 1.0 6,093,090 3

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

  • Sort Key: bill_claim.bill_no, bill_claim.claim_id
  • Sort Method: external merge Disk: 261256kB
17. 3,646.289 3,646.289 ↓ 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.195..3,646.289 rows=6,093,095 loops=3)

18. 0.042 0.042 ↑ 1.0 1 2,056,225

Index Scan using bill_pkey on bill b (cost=0.56..4.05 rows=1 width=45) (actual time=0.042..0.042 rows=1 loops=2,056,225)

  • Index Cond: ((bill_no)::text = (bc.bill_no)::text)
  • Filter: (status = ANY ('{F,C}'::bpchar[]))
  • Rows Removed by Filter: 0
19. 62,369.666 62,369.666 ↑ 1.0 1 1,641,307

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..3.88 rows=1 width=131) (actual time=0.038..0.038 rows=1 loops=1,641,307)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
Planning time : 72.912 ms
Execution time : 198,378.527 ms