Memory consumption MIMIC-III

Question

How much memory is needed to join (psql) the patient and labevent table using:

SELECT 
  patients.subject_id, 
  patients.gender, 
  patients.dob, 
  patients.dod, 
  patients.dod_hosp, 
  patients.dod_ssn, 
  patients.hospital_expire_flag, 
  labevents.subject_id, 
  labevents.hadm_id, 
  labevents.itemid, 
  labevents.charttime, 
  labevents.value, 
  labevents.uom
FROM 
  mimiciii.patients, 
  mimiciii.labevents
WHERE 
  patients.subject_id = labevents.subject_id;

It might be interesting to define the hardware requirements for researchers analyzing the MIMICIII database.

Answer 1

The MIMIC III datasets can be downloaded as comma separated value (CSV) files. This means you can either (i) load the data into your favourite database and query it through that, or, (ii) load the data into a Hadoop cluster where you can query the text files using Pig, Hive or Spark. There are several trade-offs to either approach which you should consider before deciding which is best for you.

If you are running lots of complex queries or haven't got access to a high spec single machine to run the database instance, then take a look at Hadoop cluster options (AWS EMR, GCE Hadoop and Azure HDInsight are all in-cloud variants) as those will alleviate your memory issues through horizontal scaling.

Answer 2

It really depends on the kind of queries you need to run. Looking at the size of the tables you need to join is the most important. Also, DBMS allow to set the maximum memory (e.g. postgresql.conf in Postgres).

You can use the keyword EXPLAIN to display the query plan, e.g.:

EXPLAIN SELECT 
  patients.subject_id, 
  patients.gender, 
  patients.dob, 
  patients.dod, 
  patients.dod_hosp, 
  patients.dod_ssn, 
  patients.hospital_expire_flag, 
  labevents.subject_id, 
  labevents.hadm_id, 
  labevents.itemid, 
  labevents.charttime, 
  labevents.value, 
  labevents.uom
FROM 
  mimiciii.patients, 
  mimiciii.labevents
WHERE 
  patients.subject_id = labevents.subject_id;

outputs on my system:

"Hash Join  (cost=1428.70..1111155.98 rows=27872576 width=68)"
"  Hash Cond: (labevents.subject_id = patients.subject_id)"
"  ->  Seq Scan on labevents  (cost=0.00..552275.76 rows=27872576 width=28)"
"  ->  Hash  (cost=847.20..847.20 rows=46520 width=40)"
"        ->  Seq Scan on patients  (cost=0.00..847.20 rows=46520 width=40)"

Given that the patients table is small, you don't need much memory for that one. (putting labevents in memory would only be useful if you plan to make other such queries on labevents).

Table sizes (MIMIC-III v1.2):

admissions              19 MB   
callout                 13 MB   
caregivers              1176 kB 
chartevents             68 GB   
cptevents               120 MB  
d_cpt                   64 kB   
d_icd_diagnoses         4208 kB 
d_icd_procedures        1040 kB 
d_items                 4128 kB 
d_labitems              304 kB  
datetimeevents          2196 MB 
diagnoses_icd           87 MB   
drgcodes                32 MB   
icustayevents           16 MB   
ioevents                16 GB   
labevents               5707 MB 
microbiologyevents      86 MB   
noteevents              4214 MB 
patients                7072 kB 
prescriptions           1303 MB 
procedures_icd          33 MB   
services                12 MB   
transfers               63 MB