Thread: Configuration knobs & dials to speed up query optimization

Configuration knobs & dials to speed up query optimization

From
Ron Johnson
Date:
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

Re: Configuration knobs & dials to speed up query optimization

From
Laurenz Albe
Date:
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



Re: Configuration knobs & dials to speed up query optimization

From
Ron Johnson
Date:
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.

Re: Configuration knobs & dials to speed up query optimization

From
Andreas Kretschmer
Date:

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