explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BupR : Optimization for: plan #EEOX

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 11,288.971 11,288.971 ↑ 2.4 735,365 1

CTE Scan on dentaltreatments (cost=8,824,748.64..8,860,662.02 rows=1,795,669 width=12) (actual time=10,526.273..11,288.971 rows=735,365 loops=1)

2.          

CTE dentaltreatments

3. 1,232.657 10,648.404 ↑ 2.4 735,365 1

Sort (cost=8,820,259.47..8,824,748.64 rows=1,795,669 width=24) (actual time=10,526.267..10,648.404 rows=735,365 loops=1)

  • Sort Key: dv.visit_id
  • Sort Method: quicksort Memory: 81082kB
4. 775.356 9,415.747 ↑ 2.4 735,365 1

Hash Right Join (cost=257,724.99..8,633,724.57 rows=1,795,669 width=24) (actual time=5,811.344..9,415.747 rows=735,365 loops=1)

  • Hash Cond: (c.visit_encounter_id = pe.visit_encounter_id)
5. 187.113 2,242.576 ↑ 1.2 436,541 1

Nested Loop Left Join (cost=6,859.84..626,359.07 rows=530,640 width=12) (actual time=148.238..2,242.576 rows=436,541 loops=1)

6. 634.859 745.840 ↓ 1.0 436,541 1

Bitmap Heap Scan on trx_cd_component c (cost=6,859.40..167,437.27 rows=432,224 width=20) (actual time=148.177..745.840 rows=436,541 loops=1)

  • Recheck Cond: ((type = 45) OR (type = 48))
  • Heap Blocks: exact=112594
7. 0.002 110.981 ↓ 0.0 0 1

BitmapOr (cost=6,859.40..6,859.40 rows=436,858 width=0) (actual time=110.981..110.981 rows=0 loops=1)

8. 92.991 92.991 ↓ 1.0 345,288 1

Bitmap Index Scan on trx_cd_component_type_encounter_idx (cost=0.00..5,131.07 rows=337,418 width=0) (actual time=92.991..92.991 rows=345,288 loops=1)

  • Index Cond: (type = 45)
9. 17.988 17.988 ↑ 1.1 91,316 1

Bitmap Index Scan on trx_cd_component_type_encounter_idx (cost=0.00..1,512.22 rows=99,439 width=0) (actual time=17.988..17.988 rows=91,316 loops=1)

  • Index Cond: (type = 48)
10. 1,309.623 1,309.623 ↑ 2.0 1 436,541

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

  • Index Cond: (component_id = c.component_id)
  • Heap Fetches: 63099
11. 541.392 5,662.450 ↑ 2.3 645,083 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 35235kB
12. 3,243.938 5,121.058 ↑ 2.3 645,083 1

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

  • Hash Cond: (pe.visit_id = dv.visit_id)
13. 1,145.530 1,145.530 ↑ 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.015..1,145.530 rows=4,535,759 loops=1)

14. 464.082 731.590 ↑ 1.0 381,989 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 17906kB
15. 267.508 267.508 ↑ 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.011..267.508 rows=381,989 loops=1)

16.          

SubPlan (forHash Right Join)

17. 0.000 735.365 ↑ 1.0 1 735,365

Aggregate (cost=4.31..4.32 rows=1 width=9) (actual time=0.001..0.001 rows=1 loops=735,365)

18. 735.365 735.365 ↓ 0.0 0 735,365

Index Scan using mat_dentalcharttoolcodedata_encounter_type_idx on mat_dentalcharttoolcodedata (cost=0.29..4.30 rows=1 width=9) (actual time=0.001..0.001 rows=0 loops=735,365)

  • Index Cond: ((visit_encounter_id = pe.visit_encounter_id) AND (type = c.type))
Planning time : 1.131 ms
Execution time : 11,337.597 ms