Postgres SQL de interogare lent, cu masă mare (AWS RDS)

Question 1

În prezent, masă au minim rând de până la 30 mln, și este în creștere, ori de câte ori încercați să faceți interogare de SELECTARE, se ia extrem de mult timp. Ce nevoie pentru a optimiza interogare înainte de a crește performanța bazei de date?

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

Având în vedere specifice de interogare:

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

următoarele index are potențialul de a crescut foarte mult performanta:

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

Cu toate acestea, păstrați în minte că a crea un index pe o asigurare de 30 de milioane de rând masa poate dura ceva timp.

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

2

Având în vedere specifice de interogare:

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

următoarele index are potențialul de a crescut foarte mult performanta:

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

Cu toate acestea, păstrați în minte că a crea un index pe o asigurare de 30 de milioane de rând masa poate dura ceva timp.

The Impaler

2021-11-24 03:41:16

asta înseamnă să spun că am avea mai multe caz de interogare stare, am nevoie pentru a crea fiecare indice, pentru fiecare caz? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

Sola

@sola dacă aveți nevoie de exact indice optim pentru fiecare interogare, atunci da, asta poate fi o mulțime de indicii. Dar, probabil, puteți obține departe cu ceva mai puțin optime pentru unele dintre ele. Încercați câteva pentru a vedea. Dacă aveți întrebări, asigurați-vă că pentru a include EXPLICA (ANALIZA, TAMPOANE)

jjanes

după ce creați indexul de la masina mea locale, și munca la inceput, dar dupa ceva timp, aceasta nu declanșa indici, atunci când faci interogare. Acest lucru s-a întâmplat pe serverul de producție, precum și.

Sola

@Sola Dacă interogarea nu este utilizând indexul, apoi de optimizare având în vedere un alt plan de execuție. În primul rând, asigurați-vă că statisticile din tabel sunt actualizate folosind ANALYZE public.message. Apoi, dacă problema persistă, vă rugăm să preluați planul de execuție și adăugați-l la întrebare.

The Impaler

multumesc pentru raspuns. Ieri când încercarea cu indicele (company_id, project_id, attribute_id, snapshot_ts), în stare folosind (snapshot_ts și attribute_id), se lucrează la început, apoi nu. Acum am adăuga un alt indice cu (attribute_id, snapshot_ts), pentru acest scop, părea să funcționeze din nou, va continua să monitorizeze.

Sola

Postgres SQL de interogare lent, cu masă mare (AWS RDS)

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie