Configuration knobs & dials to speed up query optimization

[Ron Johnson <ronljohnsonjr@xxxxxxxxx>]

Pg 9.6.24, which will change by April, but not now.

We've got some huge (2200 line long) queries that are many UNIONs of complicated queries hitting inheritance-partitioned tables.  They can't be refactored immediately, and maybe not at all (complicated applications hitting normalized databases make for complicated queries).

BIND (and EXPLAIN, when I extract them from the log file and run them myself) takes upwards of 25 seconds.  It's from JDBC connections, if that matters.

Is there any way for me to speed that up?

The Linux system has 128GB RAM, 92% of it being "cached", according to top(1).

I've read https://www.postgresql.org/docs/9.6/runtime-config-query.html but can't go mucking around with big sticks on a very busy system with lots of concurrent users.

Here are the only non-default config values which I can think of that are relevant to the question at hand:

shared_buffers = 16GB
work_mem = 300MB
maintenance_work_mem = 12GB
effective_cache_size = 96GB 

default_statistics_target = 200

Thanks