Конфигурация производительности кажется неэффективной PostgreSQL

Question

Я не очень опытен в базах данных, и я хочу повысить производительность запросов PostgreSQL с помощью конфигурации. Большая часть запроса занимает около 3.5 секунд, чтобы полностью искать в логах. Затем я проверил файл конфигурации и настройки установлены на относительно низкие значения, поэтому я рассчитал наилучшую возможную конфигурацию и ввел ее в psql:

postgres=# ALTER SYSTEM SET
postgres-#  max_connections = '200';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  shared_buffers = '4GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  effective_cache_size = '12GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  work_mem = '20971kB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  maintenance_work_mem = '1GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  min_wal_size = '1GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  max_wal_size = '2GB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  checkpoint_completion_target = '0.7';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  wal_buffers = '16MB';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  default_statistics_target = '100';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET
postgres-#  random_page_cost = '4';
ALTER SYSTEM

Сначала это не сработало, и я подумал, что перезапуск базы данных был необходим, поэтому я использовал:

SELECT pg_reload_conf();

Но прогресса по-прежнему не было. Перед настройкой work_mem было 4 MB в то время как maintenance_work_mem было 64 MB и даже после увеличения их на эти цифры время, затрачиваемое на запросы к базе данных, было очень похожим.

---

Таблица:

Таблица была создана автоматически Django (мой веб-фреймворк), поэтому вот так:

CREATE TABLE tablename (
    "id" serial NOT NULL PRIMARY KEY,
    "some_char" varchar(30) NOT NULL,
);

Создание индекса:

CREATE INDEX trgm_idx ON table USING gist (column gist_trgm_ops);

Планы выполнения (объяснить и проанализировать):

 Bitmap Heap Scan on main_question  (cost=1721.05..20990.16 rows=1786 width=436) (actual time=2634.604..2634.634 rows=9 loops=1)
   Recheck Cond: (attr1 % 'querytext'::text)
   Filter: (char_length(attr2) > 300)
   Rows Removed by Filter: 5
   Heap Blocks: exact=13
   Buffers: shared hit=419319
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..1720.60 rows=5358 width=0) (actual time=2634.578..2634.578 rows=14 loops=1)
         Index Cond: (attr1 % 'querytext'::text)
         Buffers: shared hit=419306
 Planning time: 0.131 ms
 Execution time: 2634.739 ms
(11 rows)

Журналы запросов:

до конфигурации:

(0.000) SELECT typarray FROM pg_type WHERE typname = 'citext'; args=None
(3.568) SELECT "main_model"."id", "main_model"."attribute1", "main_model"."attribute2" FROM "main_model" WHERE ((CHAR_LENGTH(attribute2) > 350) AND "main_model"."attribute1" % 'querytext'); args=(u'querytext',)

после настройки:

(0.000) SELECT typarray FROM pg_type WHERE typname = 'citext'; args=None
(3.555) SELECT "main_model"."id", "main_model"."attribute1", "main_model"."attribute2" FROM "main_model" WHERE ((CHAR_LENGTH(attribute2) > 350) AND "main_model"."attribute1" % 'querytext'); args=(u'querytext',)

как вы можете видеть, время запроса по какой-то причине очень похоже.

---

Разве эти настройки не должны увеличивать максимальное использование памяти и, следовательно, производительность? Я делаю что-то неправильно?

Answer 1

Установка максимального количества соединений на 200 - верный признак того, что вы новичок в настройке производительности Postgres. Проверьте вики о производительности или некоторые введения на YouTube. Я могу порекомендовать Кристофа Петтуса: PostgreSQL, когда это не твоя работа.