We are having 2.6M rows data within a single table and have joined with the other 2 tables having similar sizes. While running a select query it is taking 100% CPU and the query was hanging forever. We tried reindexing for all these three tables. After reindexing query is still taking 100% CPU but for 1 min and it's coming out.
We have tried the following parameters to tune the Postgres as well but no performance improvement.
shared_buffer = 23GB
work_mem = 100mb
effective_cache_size = 45GB
OS = RHEL 7
RAM = 90GB
Please do suggest what else I can try further to improve performance. Any tuning parameters apart from mentioned above. If there are many parameters then any good reference for that as well.