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