Powolny zapytanie SQL, Postgresql z dużej tabeli (AWS RDS)

Question 1

Obecnie w tabeli co najmniej 30 milionów wierszy, a ona rośnie, za każdym razem, gdy próbujesz wykonać zapytanie SELECT, to zajmuje bardzo dużo czasu. Co trzeba zoptymalizować zapytanie, zanim pokażę wam wydajność bazy danych?

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

Biorąc pod uwagę konkretne zapytanie:

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

następny indeks może znacznie zwiększyć wydajność:

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

Należy jednak pamiętać, że tworzenie indeksu w tabeli z 30 milionami wierszy może zająć trochę czasu.

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

2

Biorąc pod uwagę konkretne zapytanie:

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

następny indeks może znacznie zwiększyć wydajność:

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

Należy jednak pamiętać, że tworzenie indeksu w tabeli z 30 milionami wierszy może zająć trochę czasu.

The Impaler

2021-11-24 03:41:16

czy to oznacza, że, powiedzmy, że mam warunek zapytania z wieloma przypadkami, muszę stworzyć każdy indeks na każdą okazję? "(identyfikator firmy, identyfikator projektu, identyfikator atrybutu, przyciąganie)", "(identyfikator projektu, identyfikator atrybutu, przyciąganie)", "(identyfikator atrybutu, przyciąganie)"

Sola

@sola jeśli potrzebujesz dokładny optymalny indeks dla każdego żądania, to tak, to może być wiele indeksów. Ale dla niektórych z nich, prawdopodobnie na sucho kilka mniej optymalny wariant. Wypróbuj kilka i zobacz. Jeśli masz jakieś pytania, koniecznie włącz WYJAŚNIENIE (ANALIZA, BUFORY)

jjanes

po utworzeniu indeksu na moim komputerze, i pracował na początku, ale po pewnym czasie nie wszczynał indeksy po uruchomieniu kwerendy. Stało się to również na serwerze produkcyjnym.

Sola

@Sola Jeżeli wniosek nie korzysta z indeksu, to optymalizator rozważa inny plan wykonania. Po pierwsze, upewnij się, że statystyki aktualne tabele, za pomocą ANALYZE public.message. Następnie, jeśli problem nadal występuje, proszę wyjąć plan wykonania i dodaj go pytanie.

The Impaler

dziękuję za odpowiedź. Wczoraj podczas próby z indeksem (company_id, project_id, attribute_id, snapshot_ts), pod warunkiem zastosowania (snapshot_ts i attribute_id), najpierw działa, a potem nie. Teraz dodaję jeszcze jeden wskaźnik z (attribute_id, snapshot_ts), do tego celu, jak się wydaje, działa ponownie, będzie monitoring.

Sola

Powolny zapytanie SQL, Postgresql z dużej tabeli (AWS RDS)

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii