We run a custom application that produces analyzable data-sets for medical research.
the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in postgres RDBMS and data is indexed and clustered.
Our application is read intensive(read only).
Usually it took about 7-8 hours on a normal computer running on a HDD to go through all this data and produce a dataset.
Recently we brought a Highend machine (intel i5 5960x, 32GB ram with an intel p3600 2TB SSD) and set up the database and our application.
I was amazed to find out it took almost the same amount of time that it used to take on the old machine to extract a dataset.
EDIT 1 : How the program works
The program is a Java Application
There are about 13Million patient's records in the database. and each patient has 10000+ entries about their health/diagnosis/prescriptions etc. Hence the massive size.
What we do is look for patients with particular medical conditions(defined in a particular table) and pull out their medical history and other events(from different tables) from the database and do research on it.
The program is not CPU bound as we have very simple logic behind the program
I create a lot of java objects(at least 1 object per patient, which in turn is made up of lot of strings which hold values I pull out of database), hence we can say it is a bit memory intensive
Yes it is harddrive intensive as we read Billions of records, and that is why I though if i have a really fast harddrive I can get this running very fast. And hence brought a Intel P3600 2TB SSD
SAMPLE QUERY
These kind of queries are run on tables with 100,000+ of records for each patient 4-5 times per program execution.
SELECT Field1,Date
FROM schema.table1
WHERE Field1 IN ('aaaaaaa','bbbbbbb','ccccccc',...,'nnnnnnn' )
patientid = 'abcdefg'
AND Date <= 'xxxx-xx-xx'
order by Field1,Date;
Note: Field1,Date,patientID is indexed
EDIT 2:
**Explain Analyze Output:**
"GroupAggregate (cost=51.75..51.78 rows=1 width=12) (actual time=1.112..1.112 rows=1 loops=1)"
" Output: Field1, min(Date), Date"
" Group Key: medical.Date, medical.Field1"
" Buffers: shared hit=102 read=4 written=4"
" -> Sort (cost=51.75..51.76 rows=2 width=12) (actual time=1.107..1.108 rows=1 loops=1)"
" Output: Field1, Date"
" Sort Key: medical.Date, medical.Field1"
" Sort Method: quicksort Memory: 25kB"
" Buffers: shared hit=102 read=4 written=4"
" -> Index Scan using patmedmed_index on a6658.medical (cost=0.42..51.74 rows=2 width=12) (actual time=1.016..1.089 rows=1 loops=1)"
" Output: Field1, Date"
" Index Cond: ((medical.patid = '00Nb'::bpchar) AND (medical.Field1 = ANY ('{C100111,C100112,C100z00,C101.00,C101000,C101100,C101y00,C101z00,C102.00,C102000,C102100,C102z00,C103.00,C103000,C103100,C103y00,C103z00,C104.00,C104.11,C104000,C10410 (...)"
" Filter: (medical.Date <= '2015-01-01'::date)"
" Buffers: shared hit=102 read=4 written=4"
"Planning time: 0.844 ms"
"Execution time: 1.248 ms"
I really need help
explain (analyze, verbose, buffers)
for that statement. Also: are you sure Postgres is the bottleneck here? Maybe the processing of that many rows in your application limits the total throughput