Postgres SQL dotaz pomaly s veľkým stolom (AWS RDS)

Question 1

V súčasnosti tabuľky majú minimálne rad 30million, a to rastie vždy, keď sa snaží urobiť, VYBERTE dotaz, to trvať veľmi dlho. Čo je potrebné optimalizovať dotaz predtým, než som zvýšiť výkon databázy?

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

Vzhľadom na špecifické dotazu:

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

nasledujúce index má potenciál výrazne zvyšuje výkon:

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

Avšak, majte na pamäti, že vytvorenie indexu na 30-miliónov riadok tabuľky môže trvať nejaký čas.

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

2

Vzhľadom na špecifické dotazu:

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

nasledujúce index má potenciál výrazne zvyšuje výkon:

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

Avšak, majte na pamäti, že vytvorenie indexu na 30-miliónov riadok tabuľky môže trvať nejaký čas.

The Impaler

2021-11-24 03:41:16

je to, povedzme, mám viacero prípade dotazu podmienky, potrebujem vytvoriť každý index pre každý prípad? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola ak potrebujete presné optimálne index pre každý dotaz, potom áno, že môže byť veľa indexy. Ale asi sa môžete dostať preč s trochu menej optimálne pre niektoré z nich. Skúste pár a vidieť. Ak máte otázky, nezabudnite zahrnúť VYSVETLIŤ (ANALYZOVAŤ, NÁRAZNÍKY)

jjanes

po vytvoriť index na môj miestny stroj, a je to práca na začiatku, ale potom, niekedy, to nie je spúšť indexy, keď robí dotaz. To sa stalo na výrobu server rovnako.

Sola

@Sola Ak dotaz nie je pomocou indexu, potom optimalizáciu to posudzovaní rôznych vykonávania plánu. Prvý, uistite sa, že štatistiky v tabuľke sú aktuálne pomocou ANALYZE public.message. Potom, ak problém stále pretrváva, prosím vyber vykonávania plánu a pridať otázku.

The Impaler

ďakujem za odpoveď. Včera, keď sa snaží s index (company_id, project_id, attribute_id, snapshot_ts), v stave pomocou (snapshot_ts a attribute_id), sa pracovať na prvý, potom nie. Teraz som sa pridať ďalší index s (attribute_id, snapshot_ts), pre tento účel sa zdá, že opäť funkčný, bude aj naďalej monitorovať.

Sola

Postgres SQL dotaz pomaly s veľkým stolom (AWS RDS)

Otázka

Najlepšiu odpoveď

V iných jazykoch

Táto stránka je v iných jazykoch

Populárne v tejto kategórii

Obľúbené otázky v tejto kategórii