explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0yRw

Settings
# exclusive inclusive rows x rows loops node
1. 0.528 21.142 ↑ 189.0 10 1

Sort (cost=78,146.19..78,150.91 rows=1,890 width=42) (actual time=21.142..21.142 rows=10 loops=1)

  • Sort Key: (CASE WHEN (insite_patient_score.score IS NULL) THEN 0::numeric ELSE insite_patient_score.score END), (string_agg((pm.patient_ide)::text, ' ; '::text ORDER BY (pm.patient_ide)::text))
  • Sort Method: top-N heapsort Memory: 26kB
2. 11.960 20.614 ↑ 1.4 1,354 1

GroupAggregate (cost=77,940.72..78,105.34 rows=1,890 width=42) (actual time=8.566..20.614 rows=1,354 loops=1)

  • Group Key: insite_patient_score.score, pd.patient_num, pd.sex_cd, pd.birth_date
3. 1.382 8.654 ↑ 6.9 1,354 1

Sort (cost=77,940.72..77,964.22 rows=9,400 width=42) (actual time=8.547..8.654 rows=1,354 loops=1)

  • Sort Key: insite_patient_score.score, pd.patient_num, pd.sex_cd, pd.birth_date
  • Sort Method: quicksort Memory: 160kB
4. 0.813 7.272 ↑ 6.9 1,354 1

Nested Loop Left Join (cost=11.56..77,320.39 rows=9,400 width=42) (actual time=0.107..7.272 rows=1,354 loops=1)

5. 0.353 3.751 ↑ 6.9 1,354 1

Hash Left Join (cost=11.14..27,440.39 rows=9,400 width=37) (actual time=0.096..3.751 rows=1,354 loops=1)

  • Hash Cond: (pd.patient_num = insite_patient_score.patient_num)
6. 0.254 3.348 ↑ 6.9 1,354 1

Nested Loop Left Join (cost=0.99..26,736.99 rows=9,400 width=33) (actual time=0.034..3.348 rows=1,354 loops=1)

7. 0.386 0.386 ↑ 6.9 1,354 1

Index Scan using qt_idx_qpsc_riid on qt_patient_set_collection pc (cost=0.56..371.99 rows=9,400 width=4) (actual time=0.017..0.386 rows=1,354 loops=1)

  • Index Cond: (result_instance_id = 1962)
8. 2.708 2.708 ↑ 1.0 1 1,354

Index Only Scan using pd_idx_patientnum_sexcd_birthdate on patient_dimension pd (cost=0.43..2.79 rows=1 width=29) (actual time=0.001..0.002 rows=1 loops=1,354)

  • Index Cond: (patient_num = pc.patient_num)
  • Heap Fetches: 0
9. 0.017 0.050 ↓ 1.8 100 1

Hash (cost=9.43..9.43 rows=57 width=8) (actual time=0.050..0.050 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
10. 0.033 0.033 ↓ 1.8 100 1

Index Scan using insite_idx_qips_riid on insite_patient_score (cost=0.42..9.43 rows=57 width=8) (actual time=0.011..0.033 rows=100 loops=1)

  • Index Cond: (result_instance_id = 1963)
11. 2.708 2.708 ↑ 1.0 1 1,354

Index Scan using pm_patnum_idx on patient_mapping pm (cost=0.43..5.30 rows=1 width=13) (actual time=0.002..0.002 rows=1 loops=1,354)

  • Index Cond: (pc.patient_num = patient_num)
  • Filter: (((patient_ide_source)::text = 'MASTER_PATIENT_INDEX'::text) AND ((patient_ide_status)::text = 'Active'::text))