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
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) |