explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KMmR : Optimization for: Optimization for: plan #EEOX; plan #BupR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 9,542.127 9,542.127 ↑ 2.4 735,365 1

CTE Scan on dentaltreatments (cost=8,824,750.64..8,860,664.02 rows=1,795,669 width=12) (actual time=8,806.043..9,542.127 rows=735,365 loops=1)

2.          

CTE dentaltreatments

3. 1,137.288 8,930.796 ↑ 2.4 735,365 1

Sort (cost=8,820,261.47..8,824,750.64 rows=1,795,669 width=24) (actual time=8,806.035..8,930.796 rows=735,365 loops=1)

  • Sort Key: dv.visit_id
  • Sort Method: quicksort Memory: 81082kB
4. 747.191 7,793.508 ↑ 2.4 735,365 1

Hash Right Join (cost=257,724.99..8,633,726.57 rows=1,795,669 width=24) (actual time=4,725.932..7,793.508 rows=735,365 loops=1)

  • Hash Cond: (c.visit_encounter_id = pe.visit_encounter_id)
5. 399.480 1,718.438 ↑ 1.2 436,541 1

Nested Loop Left Join (cost=6,859.84..626,361.07 rows=530,640 width=12) (actual time=132.478..1,718.438 rows=436,541 loops=1)

6. 348.124 445.876 ↓ 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=132.439..445.876 rows=436,541 loops=1)

  • Recheck Cond: ((type = 45) OR (type = 48))
  • Heap Blocks: exact=112598
7. 0.004 97.752 ↓ 0.0 0 1

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

8. 82.940 82.940 ↓ 1.0 345,299 1

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

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

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

  • Index Cond: (type = 48)
10. 873.082 873.082 ↑ 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.002..0.002 rows=1 loops=436,541)

  • Index Cond: (component_id = c.component_id)
  • Heap Fetches: 63110
11. 516.616 4,592.514 ↑ 2.3 645,083 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 35235kB
12. 3,005.740 4,075.898 ↑ 2.3 645,083 1

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

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

14. 199.174 372.452 ↑ 1.0 381,989 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 17906kB
15. 173.278 173.278 ↑ 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.006..173.278 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))