explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3Kpo

Settings
# exclusive inclusive rows x rows loops node
1. 129.945 6,808.685 ↓ 1.2 3,853 1

Gather Merge (cost=230,351.97..230,723.46 rows=3,184 width=2,435) (actual time=6,693.466..6,808.685 rows=3,853 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 24.082 6,678.740 ↑ 1.2 1,284 3 / 3

Sort (cost=229,351.95..229,355.93 rows=1,592 width=2,435) (actual time=6,678.465..6,678.740 rows=1,284 loops=3)

  • Sort Key: (lower(claim_medical.date_of_service)) DESC, claim_medical.claim_medical_id
  • Sort Method: quicksort Memory: 2,157kB
  • Worker 0: Sort Method: quicksort Memory: 1,926kB
  • Worker 1: Sort Method: quicksort Memory: 1,855kB
3. 3.774 6,654.658 ↑ 1.2 1,284 3 / 3

Nested Loop Left Join (cost=95,231.38..229,267.28 rows=1,592 width=2,435) (actual time=3,056.213..6,654.658 rows=1,284 loops=3)

4. 73.510 6,627.214 ↑ 2.0 438 3 / 3

Nested Loop Anti Join (cost=95,230.95..225,725.87 rows=861 width=1,745) (actual time=3,056.087..6,627.214 rows=438 loops=3)

  • Join Filter: (claim_medical.claim_status_id <> 1)
5. 134.958 6,142.021 ↓ 93.8 82,337 3 / 3

Hash Join (cost=95,230.52..225,034.34 rows=878 width=1,745) (actual time=585.927..6,142.021 rows=82,337 loops=3)

  • Hash Cond: (policy_holder.payer_id = payer.payer_id)
6. 144.278 6,006.846 ↓ 93.8 82,337 3 / 3

Nested Loop (cost=95,214.91..225,016.39 rows=878 width=1,745) (actual time=585.624..6,006.846 rows=82,337 loops=3)

7. 104.952 5,450.885 ↓ 93.8 82,337 3 / 3

Hash Join (cost=95,214.49..224,356.84 rows=878 width=1,206) (actual time=585.599..5,450.885 rows=82,337 loops=3)

  • Hash Cond: (claim_medical.claim_status_id = claim_status.claim_status_id)
8. 389.639 5,345.904 ↓ 75.0 82,385 3 / 3

Nested Loop (cost=95,213.38..224,350.39 rows=1,098 width=1,154) (actual time=585.544..5,345.904 rows=82,385 loops=3)

  • Join Filter: ((claim_tenant.claim_medical_id = claim_medical.claim_medical_id) AND ((employer.effective_date IS NULL) OR (employer.effective_date <= lower(claim_medical.date_of_service))) AND ((member.hire_term_daterange IS NULL) OR (member.hire_term_daterange @> lower(claim_medical.date_of_service))))
  • Rows Removed by Join Filter: 45,083
9. 341.134 3,203.758 ↓ 20.8 194,723 3 / 3

Parallel Hash Join (cost=95,212.95..216,706.02 rows=9,344 width=588) (actual time=585.278..3,203.758 rows=194,723 loops=3)

  • Hash Cond: (claim_member.claim_medical_id = claim_tenant.claim_medical_id)
10. 1,058.437 2,317.407 ↓ 1.7 194,723 3 / 3

Hash Join (cost=15,093.69..136,118.47 rows=114,498 width=584) (actual time=32.992..2,317.407 rows=194,723 loops=3)

  • Hash Cond: (claim_member.member_id = member.id)
11. 1,226.924 1,226.924 ↑ 1.2 3,054,534 3 / 3

Parallel Seq Scan on claim_member (cost=0.00..105,561.67 rows=3,818,167 width=8) (actual time=0.015..1,226.924 rows=3,054,534 loops=3)

12. 12.224 32.046 ↑ 7.7 3,506 3 / 3

Hash (cost=14,756.32..14,756.32 rows=26,990 width=580) (actual time=32.045..32.046 rows=3,506 loops=3)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,112kB
13. 3.443 19.822 ↑ 7.7 3,506 3 / 3

Nested Loop (cost=0.71..14,756.32 rows=26,990 width=580) (actual time=0.081..19.822 rows=3,506 loops=3)

14. 1.212 1.212 ↑ 1.1 523 3 / 3

Index Scan using uq_employer_tenant_id_wex_key on employer (cost=0.29..164.51 rows=599 width=370) (actual time=0.046..1.212 rows=523 loops=3)

  • Index Cond: (tenant_id = 11)
  • Filter: (archived IS FALSE)
  • Rows Removed by Filter: 130
15. 15.167 15.167 ↑ 13.6 7 1,569 / 3

Index Scan using member_employer_id on member (cost=0.42..23.41 rows=95 width=210) (actual time=0.007..0.029 rows=7 loops=1,569)

  • Index Cond: (employer_id = employer.employer_id)
  • Filter: ((deleted IS FALSE) AND (archived IS FALSE))
  • Rows Removed by Filter: 1
16. 110.083 545.217 ↑ 1.3 231,220 3 / 3

Parallel Hash (cost=76,434.53..76,434.53 rows=294,778 width=8) (actual time=545.216..545.217 rows=231,220 loops=3)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 35,392kB
17. 420.532 435.134 ↑ 1.3 231,220 3 / 3

Parallel Bitmap Heap Scan on claim_tenant (cost=6,453.80..76,434.53 rows=294,778 width=8) (actual time=57.984..435.134 rows=231,220 loops=3)

  • Recheck Cond: (tenant_id = 11)
  • Heap Blocks: exact=21,597
18. 14.602 14.602 ↑ 1.0 693,661 1 / 3

Bitmap Index Scan on claim_tenant__tenant_id (cost=0.00..6,276.94 rows=707,467 width=0) (actual time=43.806..43.807 rows=693,661 loops=1)

  • Index Cond: (tenant_id = 11)
19. 1,752.507 1,752.507 ↑ 1.0 1 584,169 / 3

Index Scan using claim_medical_pkey on claim_medical (cost=0.43..0.80 rows=1 width=574) (actual time=0.009..0.009 rows=1 loops=584,169)

  • Index Cond: (claim_medical_id = claim_member.claim_medical_id)
  • Filter: (type_id <> 3)
  • Rows Removed by Filter: 0
20. 0.004 0.029 ↑ 1.0 4 3 / 3

Hash (cost=1.06..1.06 rows=4 width=52) (actual time=0.029..0.029 rows=4 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.025 0.025 ↑ 1.0 4 3 / 3

Seq Scan on claim_status (cost=0.00..1.06 rows=4 width=52) (actual time=0.024..0.025 rows=4 loops=3)

  • Filter: ((name)::text <> 'In Process'::text)
  • Rows Removed by Filter: 1
22. 411.683 411.683 ↑ 1.0 1 247,010 / 3

Index Scan using policy_holder_pkey on policy_holder (cost=0.42..0.75 rows=1 width=539) (actual time=0.005..0.005 rows=1 loops=247,010)

  • Index Cond: (policy_holder_id = claim_medical.policy_holder_id)
23. 0.057 0.217 ↑ 1.0 249 3 / 3

Hash (cost=12.49..12.49 rows=249 width=4) (actual time=0.217..0.217 rows=249 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
24. 0.160 0.160 ↑ 1.0 249 3 / 3

Seq Scan on payer (cost=0.00..12.49 rows=249 width=4) (actual time=0.032..0.160 rows=249 loops=3)

25. 411.683 411.683 ↑ 1.0 1 247,010 / 3

Index Scan using claim_medical_read_cla_read on claim_medical_read (cost=0.43..0.78 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=247,010)

  • Index Cond: ((claim_medical_id = claim_medical.claim_medical_id) AND (claim_medical.last_updated_status < read_datetime))
  • Filter: (tenant_id = 11)
  • Rows Removed by Filter: 0
26. 23.670 23.670 ↑ 9.3 3 1,315 / 3

Index Scan using claim_medical_line_claim_medical_vendor_system_id_unique on claim_medical_line claim_medical_line_1 (cost=0.43..3.83 rows=28 width=330) (actual time=0.038..0.054 rows=3 loops=1,315)

  • Index Cond: (claim_medical.claim_medical_id = claim_medical_id)
Planning time : 9.102 ms
Execution time : 6,810.497 ms