Re: Configuration knobs & dials to speed up query optimization - Mailing list pgsql-general

From Ron Johnson
Subject Re: Configuration knobs & dials to speed up query optimization
Date
Msg-id CANzqJaDiUSjj=nurJRpPOKUG8Bet7d=XRyzJaB-fkJMrR6Xb=g@mail.gmail.com
Whole thread Raw
In response to Re: Configuration knobs & dials to speed up query optimization  (Laurenz Albe <laurenz.albe@cybertec.at>)
List pgsql-general
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.

pgsql-general by date:

Previous
From: Ron Johnson
Date:
Subject: Re: pg_restore enhancements
Next
From: Andreas Kretschmer
Date:
Subject: Re: Configuration knobs & dials to speed up query optimization