explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kouUw

Settings
# exclusive inclusive rows x rows loops node
1. 15.731 31,426.648 ↑ 1.0 1 1

Aggregate (cost=13,254,548,446.33..13,254,548,446.34 rows=1 width=8) (actual time=31,426.647..31,426.648 rows=1 loops=1)

  • time=0.027..0.038 rows=2 loops=112002)
  • time=0.020..0.029 rows=2 loops=112002)
2. 1,406.323 31,410.917 ↑ 2.0 16,364 1

Index Scan using cl_npi_client_client_id on npi_client npiclient0_ (cost=0.42..13,254,548,364.74 rows=32,636 width=8) (actual time=15,855.377..31,410.917 rows=16,364 loops=1)

  • Index Cond: (client_id = 1)
  • Filter: (((alternatives: SubPlan 1 or hashed SubPlan 2) AND (provider_id_type = 2)) OR ((alternatives: SubPlan 3 or hashed SubPlan 4) AND (provider_id_type = 1)))
  • Rows Removed by Filter: 54792
3.          

SubPlan (for Index Scan)

4. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=91,910.35..108,767.67 rows=16 width=0) (never executed)

  • Join Filter: (providerli2_.claim_id = claimmaste1_.id)
5. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=91,909.92..108,715.22 rows=19 width=16) (never executed)

  • Hash Cond: (servicelin3_.claim_id = providerli2_.claim_id)
6. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=91,186.36..91,855.22 rows=66,886 width=8) (never executed)

  • Group Key: servicelin3_.claim_id
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on cl_service_line_detail servicelin3_ (cost=0.00..89,752.89 rows=573,388 width=8) (never executed)

  • Filter: ((cpt_code IS NOT NULL) AND ((cpt_code)::text ~~ '8%'::text))
8. 0.000 0.000 ↓ 0.0 0

Hash (cost=721.28..721.28 rows=183 width=8) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cl_claim_provider providerli2_ (cost=6.34..721.28 rows=183 width=8) (never executed)

  • Recheck Cond: ((provider_npi IS NOT NULL) AND ((provider_npi)::text = (npiclient0_.npi)::text))
  • Filter: ((provider_type)::text = ANY ('{85,77,82,DN}'::text[]))
10. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on cl_claim_provider_provider_npi (cost=0.00..6.29 rows=186 width=0) (never executed)

  • Index Cond: ((provider_npi IS NOT NULL) AND ((provider_npi)::text = (npiclient0_.npi)::text))
11. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cl_claim_master on cl_claim_master claimmaste1_ (cost=0.42..2.75 rows=1 width=8) (never executed)

  • Index Cond: (id = servicelin3_.claim_id)
  • Filter: ((client_id = 1) AND (recovery_status <> ALL ('{60,70,80}'::integer[])))
12. 5,042.584 15,289.438 ↓ 1.2 247,929 1

Nested Loop (cost=93,360.58..206,253.53 rows=201,250 width=10) (actual time=3,449.426..15,289.438 rows=247,929 loops=1)

  • -> Index Scan using indx_claim_provider_claim_id on cl_claim_provider providerli2__1 (cost=0.43..0.88 rows=3 width=18) (actual
13. 3,762.571 10,246.854 ↓ 1.7 112,002 1

Hash Semi Join (cost=93,360.15..147,175.15 rows=64,973 width=16) (actual time=3,449.396..10,246.854 rows=112,002 loops=1)

  • Hash Cond: (claimmaste1__1.id = servicelin3__1.claim_id)
  • Index Cond: (claim_id = claimmaste1__1.id)
  • Filter: ((provider_npi IS NOT NULL) AND ((provider_type)::text = ANY ('{85,77,82,DN}'::text[])))
  • Rows Removed by Filter: 0
14. 3,035.011 3,035.011 ↓ 1.0 721,823 1

Seq Scan on cl_claim_master claimmaste1__1 (cost=0.00..50,061.01 rows=719,855 width=8) (actual time=0.023..3,035.011 rows=721,823 loops=1)

  • Filter: ((client_id = 1) AND (recovery_status <> ALL ('{60,70,80}'::integer[])))
  • Rows Removed by Filter: 20223
15. 206.337 3,449.272 ↓ 1.7 115,600 1

Hash (cost=92,524.08..92,524.08 rows=66,886 width=8) (actual time=3,449.272..3,449.272 rows=115,600 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 4516kB
16. 1,204.444 3,242.935 ↓ 1.7 115,600 1

HashAggregate (cost=91,186.36..91,855.22 rows=66,886 width=8) (actual time=2,991.962..3,242.935 rows=115,600 loops=1)

  • Group Key: servicelin3__1.claim_id
17. 2,038.491 2,038.491 ↓ 1.0 587,855 1

Seq Scan on cl_service_line_detail servicelin3__1 (cost=0.00..89,752.89 rows=573,388 width=8) (actual time=0.014..2,038.491 rows=587,855 loops=1)

  • Filter: ((cpt_code IS NOT NULL) AND ((cpt_code)::text ~~ '8%'::text))
  • Rows Removed by Filter: 1149829
18. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=93,372.16..95,014.37 rows=34 width=0) (never executed)

  • Join Filter: (providerli5_.claim_id = claimmaste4_.id)
19. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=93,371.73..94,903.95 rows=40 width=16) (never executed)

  • Hash Cond: (providerli5_.claim_id = servicelin6_.claim_id)
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cl_claim_provider providerli5_ (cost=11.58..1,538.50 rows=392 width=8) (never executed)

  • Recheck Cond: ((federal_tax_id)::text = (npiclient0_.npi)::text)
  • Filter: ((provider_npi IS NOT NULL) AND ((provider_type)::text = ANY ('{85,77,82,DN}'::text[])))
21. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on cl_claim_provider_federal_tax_id (cost=0.00..11.48 rows=407 width=0) (never executed)

  • Index Cond: ((federal_tax_id)::text = (npiclient0_.npi)::text)
22. 0.000 0.000 ↓ 0.0 0

Hash (cost=92,524.08..92,524.08 rows=66,886 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=91,186.36..91,855.22 rows=66,886 width=8) (never executed)

  • Group Key: servicelin6_.claim_id
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on cl_service_line_detail servicelin6_ (cost=0.00..89,752.89 rows=573,388 width=8) (never executed)

  • Filter: ((cpt_code IS NOT NULL) AND ((cpt_code)::text ~~ '8%'::text))
25. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_cl_claim_master on cl_claim_master claimmaste4_ (cost=0.42..2.75 rows=1 width=8) (never executed)

  • Index Cond: (id = servicelin6_.claim_id)
  • Filter: ((client_id = 1) AND (recovery_status <> ALL ('{60,70,80}'::integer[])))
26. 4,342.880 14,715.156 ↓ 1.2 247,929 1

Nested Loop (cost=93,360.58..206,253.53 rows=201,250 width=9) (actual time=3,758.452..14,715.156 rows=247,929 loops=1)

  • -> Index Scan using indx_claim_provider_claim_id on cl_claim_provider providerli5__1 (cost=0.43..0.88 rows=3 width=17) (actual
27. 3,581.426 10,372.276 ↓ 1.7 112,002 1

Hash Semi Join (cost=93,360.15..147,175.15 rows=64,973 width=16) (actual time=3,758.423..10,372.276 rows=112,002 loops=1)

  • Hash Cond: (claimmaste4__1.id = servicelin6__1.claim_id)
  • Index Cond: (claim_id = claimmaste4__1.id)
  • Filter: ((provider_npi IS NOT NULL) AND ((provider_type)::text = ANY ('{85,77,82,DN}'::text[])))
  • Rows Removed by Filter: 0
28. 3,032.536 3,032.536 ↓ 1.0 721,823 1

Seq Scan on cl_claim_master claimmaste4__1 (cost=0.00..50,061.01 rows=719,855 width=8) (actual time=0.015..3,032.536 rows=721,823 loops=1)

  • Filter: ((client_id = 1) AND (recovery_status <> ALL ('{60,70,80}'::integer[])))
  • Rows Removed by Filter: 20223
29. 191.877 3,758.314 ↓ 1.7 115,600 1

Hash (cost=92,524.08..92,524.08 rows=66,886 width=8) (actual time=3,758.314..3,758.314 rows=115,600 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 4516kB
30. 1,156.440 3,566.437 ↓ 1.7 115,600 1

HashAggregate (cost=91,186.36..91,855.22 rows=66,886 width=8) (actual time=3,342.588..3,566.437 rows=115,600 loops=1)

  • Group Key: servicelin6__1.claim_id
31. 2,409.997 2,409.997 ↓ 1.0 587,855 1

Seq Scan on cl_service_line_detail servicelin6__1 (cost=0.00..89,752.89 rows=573,388 width=8) (actual time=0.012..2,409.997 rows=587,855 loops=1)

  • Filter: ((cpt_code IS NOT NULL) AND ((cpt_code)::text ~~ '8%'::text))
  • Rows Removed by Filter: 1149829
Planning time : 10.687 ms
Execution time : 31,461.524 ms