explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bWQK

Settings
# exclusive inclusive rows x rows loops node
1. 663.651 17,845.706 ↑ 62.6 2,866 1

HashAggregate (cost=315,232.89..317,028.14 rows=179,525 width=22) (actual time=17,843.523..17,845.706 rows=2,866 loops=1)

  • Group Key: providerli2_.provider_npi, providerli2_.federal_tax_id, providerli2_.provider_type
2. 3,495.468 17,182.055 ↑ 1.8 266,757 1

Hash Join (cost=209,171.40..311,532.68 rows=493,362 width=22) (actual time=9,971.182..17,182.055 rows=266,757 loops=1)

  • Hash Cond: (providerli2_.claim_id = claimmaste0_.id)
3. 3,715.601 3,715.601 ↑ 1.0 1,786,688 1

Seq Scan on cl_claim_provider providerli2_ (cost=0.00..88,451.40 rows=1,795,249 width=30) (actual time=0.012..3,715.601 rows=1,786,688 loops=1)

  • Filter: ((provider_type)::text = ANY ('{85,82,77,71}'::text[]))
  • Rows Removed by Filter: 518,732
4. 138.009 9,970.986 ↑ 2.2 91,195 1

Hash (cost=206,665.05..206,665.05 rows=200,508 width=32) (actual time=9,970.986..9,970.986 rows=91,195 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 5,700kB
5. 1,182.546 9,832.977 ↑ 2.2 91,195 1

Hash Join (cost=148,510.31..206,665.05 rows=200,508 width=32) (actual time=5,914.127..9,832.977 rows=91,195 loops=1)

  • Hash Cond: (claimmaste0_.id = claimexten1_.claim_id)
6. 2,736.498 2,736.498 ↑ 1.0 709,353 1

Seq Scan on cl_claim_master claimmaste0_ (cost=0.00..49,074.15 rows=709,399 width=8) (actual time=0.011..2,736.498 rows=709,353 loops=1)

  • Filter: ((client_id = 1) AND (recovery_status <> ALL ('{60,70,80}'::integer[])))
  • Rows Removed by Filter: 20,222
7. 125.521 5,913.933 ↑ 2.2 95,067 1

Hash (cost=145,944.46..145,944.46 rows=205,268 width=24) (actual time=5,913.933..5,913.933 rows=95,067 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 5,199kB
8. 1,538.803 5,788.412 ↑ 2.2 95,067 1

Hash Join (cost=95,729.64..145,944.46 rows=205,268 width=24) (actual time=3,270.380..5,788.412 rows=95,067 loops=1)

  • Hash Cond: (claimexten1_1_.id = claimexten1_.claim_id)
9. 979.408 979.408 ↑ 1.0 729,575 1

Seq Scan on cl_claim_master claimexten1_1_ (cost=0.00..44,514.09 rows=729,609 width=8) (actual time=0.002..979.408 rows=729,575 loops=1)

10. 113.022 3,270.201 ↑ 2.2 95,067 1

Hash (cost=93,151.89..93,151.89 rows=206,220 width=16) (actual time=3,270.201..3,270.201 rows=95,067 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 4,457kB
11. 744.099 3,157.179 ↑ 2.2 95,067 1

Merge Join (cost=70,990.14..93,151.89 rows=206,220 width=16) (actual time=1,710.773..3,157.179 rows=95,067 loops=1)

  • Merge Cond: (claimexten1_.claim_id = claimprovi3_.claim_id)
12. 642.443 642.443 ↑ 1.0 729,519 1

Index Only Scan using pk_cl_claim_extension on cl_claim_extension claimexten1_ (cost=0.42..19,486.27 rows=732,992 width=8) (actual time=0.019..642.443 rows=729,519 loops=1)

  • Heap Fetches: 5,248
13. 214.389 1,770.637 ↓ 1.7 95,067 1

Sort (cost=70,989.28..71,131.18 rows=56,761 width=8) (actual time=1,710.731..1,770.637 rows=95,067 loops=1)

  • Sort Key: claimprovi3_.claim_id
  • Sort Method: quicksort Memory: 7,529kB
14. 732.739 1,556.248 ↓ 1.7 95,067 1

HashAggregate (cost=65,939.65..66,507.26 rows=56,761 width=8) (actual time=1,380.165..1,556.248 rows=95,067 loops=1)

  • Group Key: claimprovi3_.claim_id
15. 691.084 823.509 ↓ 1.3 261,713 1

Bitmap Heap Scan on cl_claim_provider claimprovi3_ (cost=5,837.82..65,424.10 rows=206,220 width=8) (actual time=164.795..823.509 rows=261,713 loops=1)

  • Recheck Cond: ((federal_tax_id)::text = ANY ('{330441200,131740114," 133971298",131624096,112241326}'::text[]))
  • Filter: ((provider_type)::text = ANY ('{85,82,77,71}'::text[]))
  • Heap Blocks: exact=36,537
16. 132.425 132.425 ↑ 1.0 261,737 1

Bitmap Index Scan on cl_claim_provider_federal_tax_id (cost=0.00..5,786.27 rows=264,013 width=0) (actual time=132.425..132.425 rows=261,737 loops=1)

  • Index Cond: ((federal_tax_id)::text = ANY ('{330441200,131740114," 133971298",131624096,112241326}'::text[]))
Planning time : 12.126 ms
Execution time : 17,863.847 ms