Postgres SQL query lenta con un grande tavolo (AWS RDS)

Question 1

Attualmente la tabella minima riga di 30million, ed è in crescita, ogni volta che tenta di fare query di selezione, prendere tempo estremamente lungo. Cosa serve per ottimizzare la query prima di aumentare le prestazioni del database?

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

In considerazione della specifica query:

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

il seguente indice ha il potenziale di aumentare notevolmente le prestazioni:

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

Tuttavia, tenere a mente che la creazione di un indice per 30 milioni di riga della tabella può richiedere un certo tempo.

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

2

In considerazione della specifica query:

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

il seguente indice ha il potenziale di aumentare notevolmente le prestazioni:

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

Tuttavia, tenere a mente che la creazione di un indice per 30 milioni di riga della tabella può richiedere un certo tempo.

The Impaler

2021-11-24 03:41:16

è che dire diciamo che ho più caso condizione della query, ho bisogno di creare ogni indice per ogni caso? "(company_id, project_id, id_attributo, snapshot_ts)", "(project_id, id_attributo, snapshot_ts)", "(id_attributo, snapshot_ts)"

Sola

@sola se è necessario l'esatta ottimale indice per ogni query, allora sì che può essere un sacco di indici. Ma probabilmente si può ottenere via con un po ' meno ottimale per alcuni di loro. Provare un paio e vedere. Se avete domande, essere sicuri di includere l'SPIEGARE (ANALIZZARE, BUFFER)

jjanes

dopo la creazione dell'indice alla mia macchina locale, e il lavoro all'inizio, ma dopo qualche tempo, non innescare gli indici quando si fa la query. Questo è accaduto sul server di produzione pure.

Sola

@Sola Se la query non utilizza l'indice, poi il optimizer considerando un piano di esecuzione diversi. Innanzitutto, assicurarsi che le statistiche della tabella sono fino a data utilizzando ANALYZE public.message. Quindi, se il problema persiste, si prega di recuperare il piano di esecuzione e aggiungere alla domanda.

The Impaler

grazie per la risposta. Ieri, quando, cercando con indice (company_id, project_id, id_attributo, snapshot_ts), in condizioni di utilizzo (snapshot_ts e attribute_id), è lavoro, quindi non. Ora aggiungo un altro indice (id_attributo, snapshot_ts), per questo scopo, sembra di lavorare di nuovo, continuerà a monitorare.

Sola

Postgres SQL query lenta con un grande tavolo (AWS RDS)

Domanda

Migliore risposta

In altre lingue

Questa pagina è in altre lingue

Popolare in questa categoria

Domande popolari in questa categoria