OHDSI Home | Forums | Wiki | Github

Optimizing postgreSQL database

Implementers
#1

I have never dealt with data with this size (mainly form a lesser web dev experience), when joinning tables, the query seems to run very long

asdsafa
asdsafa766×175 19.6 KB

Does not seem there is a lot of dead tuples either (vacuum is on the way on the only 2 tables that have dead tuples)

We have done optimizations suggested on CommonDataModel/PostgreSQL at master · OHDSI/CommonDataModel · GitHub

Is there any further optimization we can do? Or if there is any test to see why our SQL database is slow? Thanks

#2

New users are only allowed to have one embedded picture per post. So there is part of the dead tuple table if one is interested.

asfagag
asfagag405×578 26.7 KB

Queries sometime can last for a few days, and indexing queries from the above github can take a few days too. I am already out of idea if it is normal.

example

CLUSTER observation USING idx_observation_person_id ;

takes a few days.

t