1

I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and will not be updated. (Most columns are omitted here to save space.)

    CREATE TABLE claims
   (
     spending double precision,
     releaseid bigint NOT NULL,
     priceinclude bytea,
     costsharing double precision,
     hcciprofode bytea,
     memberlinkeid bigint,
     age integer,
     orgid integer,
     gender character(1),
     membercity character varying(22),
     memberstate character(2),
     zip character varying(5),
     principaldiagnosis character varying(6),
     CONSTRAINT claims_pkid PRIMARY KEY (releaseid)
   )
   WITH (
     OIDS=FALSE
   );

Indexes:
"claims_pkid" PRIMARY KEY, btree (releaseid)
"claims_age_idx" btree (age)
"claims_memberlinkeid_idx" btree (memberlinkeid)
"claims_principaldiagnosis_idx" btree (principaldiagnosis)
"claims_serviceprovider_idx" btree (serviceprovider)
"claims_zip_idx" btree (zip)

The indexes shown above are the only ones I've added so far. I am running the database using Windows 7 with the following hardware:

  • 64-bit Windows 7
  • 32.0 GB RAM
  • Intel Core i7-4770 CPU @ 3.40 GHz

My main question involves what I can do to improve query speed without buying a dedicated server. I am trying to demonstrate a proof-of-concept to my supervisors to show them that SQL would be faster than the statistical package the currently use, STATA. I have optimized the default settings of PostgreSQL as follows:

shared_buffers=8GB
effective_cache_size=24GB
work_mem=1GB
maintenance_work_mem=1GB
checkpoint_segments=128
checkpoint_completion=0.9
wal_buffers=-1
default_stat_target=500

However, the queries are still slow. For example, the following query

EXPLAIN ANALYZE VERBOSE
SELECT * FROM claims
WHERE gender='F'
AND age BETWEEN 16 AND 20
LIMIT 100000;

returns this:

Limit  (cost=0.00..226622.43 rows=100000 width=427) (actual time=0.029..9509.028 rows=100000 loops=1)
Output: spending, releaseid, priceinclude, costsharing, hcciprofcode, htype, 
hcciotpcode, drgmpr, medsurg, typecode, claimcat, memberlinkeid, age, orgid, 
insurancetype, gender, membercity, memberstate, zip, admissiondate, serviceprovider, 
servicenpi, ser (...)"
->  Seq Scan on public.claims  (cost=0.00..4778859.76 rows=2108732 width=427) 
(actual time=0.028..9497.420 rows=100000 loops=1)"
Output: spending, releaseid, priceinclude, costsharing, 
hcciprofcode, htype, hcciotpcode, drgmpr, medsurg, 
typecode, claimcat, memberlinkeid, age, orgid, 
insurancetype, gender, membercity, memberstate, 
zip, admissiondate, serviceprovider, servicenp (...)"
Filter: ((claims.age >= 16) AND (claims.age <= 20) AND (claims.gender = 'F'::bpchar))"
"Rows Removed by Filter: 3194024"
"Planning time: 0.190 ms"
"Execution time: 9519.455 ms"

Should I be creating a new view or a smaller table to access frequently used columns? I have never created a database this large so I am quite perplexed as to what to do!

UPDATE: My goal is to be able to query this database in a reasonable amount of time for exploratory data analysis. 15 seconds for queries is reasonable; 5 minutes for queries on the whole table is not. I am not sure how best to deal with this large table in order to achieve that goal, which is what I would like help with. (Apologies if this is a little vague.)

  • 2
    Please edit your question and add the query in question and the execution plan generated through explain (analyze, verbose). Apart from that: shared_buffers should be something like 8GB, work_mem and maintenance_work_mem should be much lower. And wal_buffers should be set to -1 to let Postgres choose the correct size. Unrelated, but: you should really avoid quoted identifiers. They are much more trouble then they are worth it. – a_horse_with_no_name Jul 25 '16 at 21:23
  • Thanks for the suggestions. I've edited the question to include the output from EXPLAIN ANALYZE and the changes to the server configuration. Let me know if you need any more information. – user101001 Jul 26 '16 at 15:04
  • 1
    Your EXPLAIN ANALYZE shows a sequential scan. If the index you have mentioned in your question is the ONLY index, then the query you have given us is not able to use that index. Also, it's not clear if the index you mention is only one index, or actually 4 different one-column indexes. – Joishi Bodio Jul 26 '16 at 15:23
  • 1
    Can you show us the index on the Age column please? Also, 9 seconds to select 100,000 rows without an index is pretty fast. – Andrew Brennan Jul 26 '16 at 15:31
  • 1
    @Alexandros I seriously doubt that. – ypercubeᵀᴹ Jul 29 '16 at 14:36
-3

For your example query: SELECT * FROM claims WHERE gender='F' AND age BETWEEN 16 AND 20 LIMIT 100000; try to add index on age. But I suggest to use "SQL SERVER PROFILER" and "Database Engine Tuning Adviser" to monitor your queries, and then add suitable indexes, and make suitable changes.

| improve this answer | |
  • 1
    Did you realize this question was about PostgreSQL? Also that there is already an index on age? – dezso Jul 29 '16 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy