Обсуждение: Configuration knobs & dials to speed up query optimization
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
work_mem = 300MB
maintenance_work_mem = 12GB
effective_cache_size = 96GB
default_statistics_target = 200
Thanks
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