explain.depesz.com

PostgreSQL's explain analyze made readable

Result: N1IY

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 11.970 ↑ 1.0 10 1

Limit (cost=78,155.66..78,155.69 rows=10 width=42) (actual time=11.967..11.970 rows=10 loops=1)

2. 0.422 11.966 ↑ 94.5 20 1

Sort (cost=78,155.64..78,160.36 rows=1,890 width=42) (actual time=11.965..11.966 rows=20 loops=1)

  • Sort Key: (CASE WHEN (insite_patient_score.score IS NULL) THEN 0::numeric ELSE insite_patient_score.score END)
  • Sort Method: top-N heapsort Memory: 27kB
3. 2.401 11.544 ↑ 1.4 1,354 1

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

  • Group Key: insite_patient_score.score, pd.patient_num, pd.sex_cd, pd.birth_date
4. 1.391 9.143 ↑ 6.9 1,354 1

Sort (cost=77,940.72..77,964.22 rows=9,400 width=42) (actual time=9.006..9.143 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
5. 1.096 7.752 ↑ 6.9 1,354 1

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

6. 0.447 3.948 ↑ 6.9 1,354 1

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

  • Hash Cond: (pd.patient_num = insite_patient_score.patient_num)
7. 0.396 3.448 ↑ 6.9 1,354 1

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

8. 0.344 0.344 ↑ 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.021..0.344 rows=1,354 loops=1)

  • Index Cond: (result_instance_id = 1962)
9. 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.002..0.002 rows=1 loops=1,354)

  • Index Cond: (patient_num = pc.patient_num)
  • Heap Fetches: 0
10. 0.018 0.053 ↓ 1.8 100 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
11. 0.035 0.035 ↓ 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.012..0.035 rows=100 loops=1)

  • Index Cond: (result_instance_id = 1963)
12. 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))
Planning time : 0.682 ms
Execution time : 12.143 ms