On Thu, Nov 23, 2023 at 3:48 AM Laurenz Albe <laurenz.a...@cybertec.at> wrote:
> 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... > Certainly not all; this one is a too-frequent exception. Quite a few take 3-5 seconds, but many more are faster. > > 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. I expected that, but am disappointed. > Other relevant parameters would be "join_collapse_limit" and > "from_collapse_limit". > I'll try setting them at the session level. > But without knowing your query, we can say nothing. > Also expected that. Can't post it, though. It's got lots of sub-selects and "selects in columns" (if that makes sense; I don't know the official term for this kind of construct: SELECT x.foo , (SELECT bar FROM blarge WHERE bar = x.id) as snog FROM snaggle x; It's well-supported by indices, too; any seq scans are for tiny "code to description" tables. Thanks.
