Postgresql – Tool to find CPU utilization for a particular query in Postgres

The auto_explain module might be what you are looking for.

The statistics collector is probably the closest you can get to the Oracle v$ views.

Some more might be available in the pg_stat_statements extension.

The default settings in postgresql.conf are very conservative and normally pretty low.

I suggest the following changes:

• raise shared_buffers to 1/8 of the complete memory, but not more than 4GB in total.
• set effective_cache_size to total memory available for postgresql - shared_buffers (effectively the memory size the system has for file caching)
• if you are running on SSDs you can also lower random_page_cost to 110% of seq_page_cost, but you should test this change if it has an effect. It basically makes postgres to go for index scans instead of sequentical scans

The postgres wiki has some more information about tuning the base paramters: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Also you should look into making the process run in parallel instead of one table after another, if this is possible. This would allow your process to utilize the full power of your hardware.