Configuration knobs & dials to speed up query optimization

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

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

Am 22.11.23 um 17:13 schrieb Ron Johnson:

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

out of support since many years!

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

As Laurenz said, default_statistics_target is too high at 200 in my
opinion. It makes all planning more expensive and increases internal
statistics. It is better to do this specifically for selected
tables/columns.

Andreas

--
Andreas Kretschmer - currently still (garden leave)
Technical Account Manager (TAM)
www.enterprisedb.com