On Wed, 2023-11-22 at 11:13 -0500, Ron Johnson wrote: > 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. Well, the system cannot support a lot of concurrent users if queries take 25 seconds to plan... > 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 The only parameter that should affect query planning time is the "default_statistics_target". The more, the longer. Other relevant parameters would be "join_collapse_limit" and "from_collapse_limit". But without knowing your query, we can say nothing. Yours, Laurenz Albe
