Re: Configuration knobs & dials to speed up query optimization

On Thu, Nov 23, 2023 at 3:48 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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.