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 https://github.com/OHDSI/CommonDataModel/tree/master/PostgreSQL

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.