Take a "snapshot" of what the server is doing about 10 times per second. Write this to a file. After N hours you can aggregate the file. What does the server do most of the time? Which tables/index gets used the most.
Before optimizing a database, I would like to know what is going on in the production system.
I know that there are internal tables like pg_stat_statements. But I guess doing a snapshot every N millseconds will present a better picture of what is going in in real life.