Медленный SQL-запрос Postgres с большой таблицей (AWS RDS)

Question 1

В настоящее время в таблице минимум 30 миллионов строк, и она растет, всякий раз, когда вы пытаетесь выполнить запрос SELECT, это занимает очень много времени. Что нужно оптимизировать запрос, прежде чем я увеличу производительность базы данных?

POSTGRES 12 on AWS RDS db.t3.small, with 20GB storage

**Message Table**

id (bigint) -> pk
meta (jsonb)
snapshot_ts (integer) -> epoch timestamp
value (character varying 100)
type (character varying 50)
created (timestamp with timezone)
last_modified (timestamp with timezone)
attribute_id (bigint) -> Foreign Key
company_id (bigint) -> Foreign Key
project_id (bigint) -> Foreign Key
device_id (bigint) -> Foreign Key


EXPLAIN (analyze,buffers) SELECT COUNT(*) FROM public.message
WHERE company_id=446 AND project_id=52 AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 AND attribute_id=458

->Aggregate  (cost=399804.26..399804.27 rows=1 width=8) (actual time=65150.696..65150.697 rows=1 loops=1)
  Buffers: shared hit=170 read=115437 dirtied=167
  I/O Timings: read=64396.424
  ->  Index Scan using message_attribute_id_6578b282 on message  (cost=0.56..399803.23 rows=411 width=0) (actual time=57752.297..65147.391 rows=8656 loops=1)
        Index Cond: (attribute_id = 458)
        Filter: ((company_id = 446) AND (project_id = 52) AND ((snapshot_ts)::numeric >= 1637568000.0) AND ((snapshot_ts)::numeric <= 1637654399.0))
        Rows Removed by Filter: 106703
        Buffers: shared hit=170 read=115437 dirtied=167
        I/O Timings: read=64396.424
Planning Time: 0.779 ms
Execution Time: 65150.730 ms

**Indexes**
indexname                       | indexdef
message_attribute_id_6578b282   | CREATE INDEX message_attribute_id_6578b282 ON public.message USING btree (attribute_id)
message_company_id_cef5ed5f     | CREATE INDEX message_company_id_cef5ed5f ON public.message USING btree (company_id)
message_device_id_b4da2571      | CREATE INDEX message_device_id_b4da2571 ON public.message USING btree (device_id)
message_pkey                    | CREATE UNIQUE INDEX message_pkey ON public.message USING btree (id)
message_project_id_7ba6787d     | CREATE INDEX message_project_id_7ba6787d ON public.message USING btree (project_id)

Question 2

Учитывая конкретный запрос:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

следующий индекс может значительно повысить производительность:

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

Однако имейте в виду, что создание индекса в таблице с 30 миллионами строк может занять некоторое время.

The Impaler · Answer 1 · 2021-11-24T03:41:16

2

Учитывая конкретный запрос:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

следующий индекс может значительно повысить производительность:

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

Однако имейте в виду, что создание индекса в таблице с 30 миллионами строк может занять некоторое время.

The Impaler

2021-11-24 03:41:16

означает ли это, что, допустим, у меня есть условие запроса с несколькими случаями, мне нужно создать каждый индекс для каждого случая? "(идентификатор компании, идентификатор проекта, идентификатор атрибута, привязка)", "(идентификатор проекта, идентификатор атрибута, привязка)", "(идентификатор атрибута, привязка)"

Sola

@sola если вам нужен точный оптимальный индекс для каждого запроса, то да, это может быть много индексов. Но для некоторых из них вам, вероятно, сойдет с рук несколько менее оптимальный вариант. Попробуйте несколько и посмотрите. Если у вас есть вопросы, обязательно включите ОБЪЯСНЕНИЕ (АНАЛИЗ, БУФЕРЫ)

jjanes

после создания индекса на моем локальном компьютере, и он работал в начале, но через некоторое время он не запускал индексы при выполнении запроса. Это также произошло на производственном сервере.

Sola

@Sola Если запрос не использует индекс, то оптимизатор рассматривает другой план выполнения. Во-первых, убедитесь, что статистика таблицы актуальна, используя ANALYZE public.message. Затем, если проблема все еще сохраняется, пожалуйста, извлеките план выполнения и добавьте его в вопрос.

The Impaler

спасибо за ответ. Вчера при попытке с индексом (company_id, project_id, attribute_id, snapshot_ts), при условии использования (snapshot_ts и attribute_id), сначала он работает, а затем нет. Теперь я добавляю еще один индекс с (attribute_id, snapshot_ts), для этой цели, похоже, снова работает, продолжит мониторинг.

Sola

Медленный SQL-запрос Postgres с большой таблицей (AWS RDS)

Вопрос

Лучший ответ

На других языках

Эта страница на других языках

Популярное в этой категории

Популярные вопросы в этой категории