Postgres SQL dotazu slow s velkým stolem (AWS RDS)

Question 1

V současné době tabulky mají minimální řadě o 30 milionů, a to je rostoucí, kdykoliv se snaží udělat SELECT dotazu, je to trvat velmi dlouhou dobu. Co třeba optimalizovat dotaz, než jsem se zvýšit výkon databáze?

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

S ohledem na konkrétní dotaz:

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

následující index má potenciál výrazně zvyšuje výkon:

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

Nicméně, mějte na paměti, že vytváření indexu na 30 milionů řádek tabulky může nějakou dobu trvat.

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

2

S ohledem na konkrétní dotaz:

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

následující index má potenciál výrazně zvyšuje výkon:

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

Nicméně, mějte na paměti, že vytváření indexu na 30 milionů řádek tabulky může nějakou dobu trvat.

The Impaler

2021-11-24 03:41:16

je to tedy, řekněme, mám více případě dotazu podmínku, potřebuji vytvořit každý index pro každý případ? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola, pokud budete potřebovat přesné optimální index pro každý dotaz, tak ano, to může být hodně indexy. Ale pravděpodobně můžete dostat pryč s poněkud méně optimální pro některé z nich. Zkuste pár a vidět. Pokud máte nějaké dotazy, ujistěte se, že zahrnout VYSVĚTLIT (ANALYZOVAT, NÁRAZNÍKY)

jjanes

po vytvoření indexu v mém lokálním počítači, a to funguje na začátku, ale poté, co někdy, to není vyvolat indexy, když děláte dotaz. To se stalo na produkční server stejně.

Sola

@Sola, Pokud dotaz není pomocí indexu, pak optimalizátor to zvažování různých provedení plánu. Za prvé, ujistěte se, že statistiky v tabulce jsou až do data pomocí ANALYZE public.message. Pak, pokud problém stále přetrvává, prosím, načíst plán vykonání a přidat ji na otázku.

The Impaler

děkuji za odpověď. Včera, když se snaží s index (company_id, project_id, attribute_id, snapshot_ts), v stavu pomocí (snapshot_ts a attribute_id), to funguje na první, pak ne. Teď jsem se přidat další index s (attribute_id, snapshot_ts), pro tento účel, zdá se, bude i nadále sledovat.

Sola

Postgres SQL dotazu slow s velkým stolem (AWS RDS)

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii