Configuration knobs & dials to speed up query optimization

Поиск
Список
Период
Сортировка
От Ron Johnson
Тема Configuration knobs & dials to speed up query optimization
Дата
Msg-id CANzqJaDutVhPYaJEjnrCryQN+mWnTWdK4Z2iiU8EheRQDF0RUQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: Configuration knobs & dials to speed up query optimization
Re: Configuration knobs & dials to speed up query optimization
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Feature request: pg_get_tabledef(text)
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: LibPQ: PQresultMemorySize as proxy to transfered bytes