explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EEOX

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,162,251.582 1,162,251.582 ↑ 2.6 735,365 1

CTE Scan on dentaltreatments (cost=769,693,644.04..769,732,499.70 rows=1,942,783 width=12) (actual time=1,161,530.407..1,162,251.582 rows=735,365 loops=1)

2.          

CTE dentaltreatments

3. 1,019.577 1,161,652.852 ↑ 2.6 735,365 1

Sort (cost=769,688,787.09..769,693,644.04 rows=1,942,783 width=24) (actual time=1,161,530.400..1,161,652.852 rows=735,365 loops=1)

  • Sort Key: dv.visit_id
  • Sort Method: quicksort Memory: 81082kB
4. 2,895.441 1,160,633.275 ↑ 2.6 735,365 1

Hash Right Join (cost=258,466.54..769,485,866.38 rows=1,942,783 width=24) (actual time=4,715.465..1,160,633.275 rows=735,365 loops=1)

  • Hash Cond: (c.visit_encounter_id = pe.visit_encounter_id)
5. 361.426 3,785.357 ↑ 1.2 436,502 1

Nested Loop Left Join (cost=7,601.38..603,206.54 rows=530,641 width=12) (actual time=134.554..3,785.357 rows=436,502 loops=1)

6. 1,135.228 1,241.421 ↓ 1.1 436,502 1

Bitmap Heap Scan on trx_cd_component c (cost=7,600.95..167,682.62 rows=399,495 width=20) (actual time=134.445..1,241.421 rows=436,502 loops=1)

  • Recheck Cond: ((type = 45) OR (type = 48))
  • Heap Blocks: exact=116714
7. 0.003 106.193 ↓ 0.0 0 1

BitmapOr (cost=7,600.95..7,600.95 rows=403,778 width=0) (actual time=106.193..106.193 rows=0 loops=1)

8. 93.247 93.247 ↓ 1.2 359,102 1

Bitmap Index Scan on trx_cd_component_type_idx (cost=0.00..5,715.44 rows=311,868 width=0) (actual time=93.247..93.247 rows=359,102 loops=1)

  • Index Cond: (type = 45)
9. 12.943 12.943 ↓ 1.0 96,213 1

Bitmap Index Scan on trx_cd_component_type_idx (cost=0.00..1,685.76 rows=91,910 width=0) (actual time=12.943..12.943 rows=96,213 loops=1)

  • Index Cond: (type = 48)
10. 2,182.510 2,182.510 ↑ 2.0 1 436,502

Index Only Scan using trx_cd_component_item_component_id_idx on trx_cd_component_item ci (cost=0.43..1.07 rows=2 width=8) (actual time=0.005..0.005 rows=1 loops=436,502)

  • Index Cond: (component_id = c.component_id)
  • Heap Fetches: 63060
11. 314.256 4,576.982 ↑ 2.3 645,083 1

Hash (cost=232,582.25..232,582.25 rows=1,462,632 width=20) (actual time=4,576.982..4,576.982 rows=645,083 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 35235kB
12. 2,770.354 4,262.726 ↑ 2.3 645,083 1

Hash Right Join (cost=22,141.75..232,582.25 rows=1,462,632 width=20) (actual time=297.380..4,262.726 rows=645,083 loops=1)

  • Hash Cond: (pe.visit_id = dv.visit_id)
13. 1,195.277 1,195.277 ↑ 1.0 4,535,759 1

Seq Scan on mat_patientencounter pe (cost=0.00..150,456.59 rows=4,535,759 width=16) (actual time=0.010..1,195.277 rows=4,535,759 loops=1)

14. 75.680 297.095 ↑ 1.0 381,989 1

Hash (cost=17,366.89..17,366.89 rows=381,989 width=12) (actual time=297.095..297.095 rows=381,989 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 17906kB
15. 221.415 221.415 ↑ 1.0 381,989 1

Seq Scan on mat_dentalvisitdata dv (cost=0.00..17,366.89 rows=381,989 width=12) (actual time=0.019..221.415 rows=381,989 loops=1)

16.          

SubPlan (forHash Right Join)

17. 735.365 1,149,375.495 ↑ 1.0 1 735,365

Aggregate (cost=395.62..395.63 rows=1 width=9) (actual time=1.563..1.563 rows=1 loops=735,365)

18. 1,148,640.130 1,148,640.130 ↓ 0.0 0 735,365

Seq Scan on mat_dentalcharttoolcodedata (cost=0.00..395.62 rows=1 width=9) (actual time=1.547..1.562 rows=0 loops=735,365)

  • Filter: ((visit_encounter_id = pe.visit_encounter_id) AND (type = c.type))
  • Rows Removed by Filter: 16119