Configuration knobs & dials to speed up query optimization

From: Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Configuration knobs & dials to speed up query optimization
Date: 2023-11-22 16:13:23
Message-ID: CANzqJaDutVhPYaJEjnrCryQN+mWnTWdK4Z2iiU8EheRQDF0RUQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2023-11-22 16:16:37 Re: LibPQ: PQresultMemorySize as proxy to transfered bytes
Previous Message Laurenz Albe 2023-11-22 16:09:39 Re: Feature request: pg_get_tabledef(text)