Início / dba / Perguntas / 341732
Accepted
Perennialista
Asked: 2024-08-18 01:42:09 +0800 CST

Otimize a consulta dizendo ao Postgres para escanear os registros do mais recente para o mais antigo

  • 772

Estou usando o Postgres 12 e no meu aplicativo tenho uma tabela que estou usando para armazenar eventos específicos que contêm informações sobre coisas que aconteceram fora do sistema e relacionadas a alguns registros no meu BD. A tabela se parece com isso:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    eventable_type VARCHAR(255) NOT NULL,
    eventable_id BIGINT NOT NULL,
    type VARCHAR(255) NOT NULL,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
);
CREATE INDEX index_events_on_eventable ON events (eventable_type, eventable_id);

Por exemplo: uma reunião foi agendada no Google Agenda. Um evento é criado nesta tabela com os detalhes do que aconteceu e o registro é associado à representação interna da reunião no BD. O dataatributo contém os detalhes do evento que também contêm um id exclusivo como:

INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 1234, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "abcdef1234"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "created", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "updated", "GoogleId": "dsfsdf2343"}'::jsonb);
INSERT INTO events (eventable_type, eventable_id, type, data) VALUES ('MyInternalEvent', 5678, 'GoogleCalendarEvent', '{"action": "deleted", "GoogleId": "dsfsdf2343"}'::jsonb);

Eu consulto a tabela de eventos assim:

SELECT * FROM events WHERE events.type = 'GoogleCalendarEvent' AND (data->>'GoogleId' = 'abcdef1234') LIMIT 1;

Em termos de cardinalidade de operações, o número de gravações é aproximadamente 3 vezes maior que o número de leituras . Ou seja: escrevemos mais do que lemos. A tabela tem cerca de 3 milhões de linhas, crescendo rapidamente. Cerca de 300 mil linhas são adicionadas à tabela todos os dias.

No momento, armazenamos apenas um outro typeevento na tabela, vamos chamá-lo de GoogleEmailEvent. Filtrar por GoogleCalendarEventretornaria aproximadamente 50% dos registros na tabela. Filtrar por GoogleIdnormalmente retornaria menos de 10 registros, mas realmente precisamos de apenas 1 porque todos eles estão associados ao mesmo "Eventable", como você pode ver nas inserções de exemplo.

Quero melhorar o tempo de execução da consulta, pensei em:

  • adicionando um índice WHERE data->>'GoogleId' IS NOT NULL. Mas estou preocupado em deixar as gravações mais lentas
  • armazenando data->>'GoogleId'em uma tabela separada junto com o id do evento para permitir uma recuperação rápida. Quão eficaz isso seria? Isso também tornaria as gravações um pouco mais lentas.
  • indexando created_ate usando isso na consulta para restringir os registros na consulta de alguma forma

Detalhe importante: A grande maioria das vezes (99% das vezes ou mais) o evento correspondente é aquele que foi inserido na tabela recentemente (digamos, em 10 minutos). Posso aproveitar esses detalhes para acelerar a consulta? Adicionar resolveria ORDER BY Id DESC LIMIT 1o problema?

postgresql

1 respostas

  1. Best Answer

    Noções básicas

    Você pode adicionar um índice parcial na expressão como você já ponderou:

    CREATE INDEX events_special_idx ON events ((data->>'GoogleId'))
WHERE  type = 'GoogleCalendarEvent';

    Consulta:

    SELECT *
FROM   events
WHERE  type = 'GoogleCalendarEvent'
AND    data->>'GoogleId' = 'abcdef1234'
LIMIT  1;

    Mas isso não é muito útil, ainda. O índice parcial não faz muito sentido enquanto a condição remove apenas metade das suas linhas. Ele pode ser melhorado de muitas maneiras.

    Otimizar passo 1

    Sua tabela está crescendo rapidamente, assim como os índices adicionados. Sua consulta precisa principalmente de entradas recentes. Adicione um timestamp de corte para reduzir o tamanho drasticamente:

    -- DROP INDEX CONCURRENTLY IF EXISTS events_special_idx;
CREATE INDEX CONCURRENTLY events_special_idx ON events ((data->>'GoogleId'))
WHERE  type = 'GoogleCalendarEvent'
AND    created_at >= '2024-08-20+0';  -- !

    Adicione o mesmo corte (ou um registro de data e hora posterior) à sua consulta para que o Postgres saiba que o índice é aplicável:

    SELECT *
FROM   events
WHERE  type = 'GoogleCalendarEvent'
AND    data->>'GoogleId' = 'abcdef1234'
AND    created_at >= '2024-08-20+0'  -- !
LIMIT  1;

    Começo com hoje (horário UTC). O índice continuará crescendo. Você tem que recriá-lo de tempos em tempos para mantê-lo pequeno. Como com um cron-job diário. Adicionei CONCURRENTLYpara não bloquear gravações.

    Ainda não é o ideal. O Postgres não pode usar varreduras somente de índice com essa expressão e tem que introspectar a jsonbcoluna (possivelmente grande?) toda vez. Além disso, a expressão torna as gravações no índice um pouco mais caras também.

    Otimizar passo 2

    O Google ID parece estar presente o tempo todo (ou na maioria das vezes). Uma coluna dedicada seria muito melhor. Na verdade, se seu documento JSON for regular, seria muito mais eficiente armazenar todas as colunas simples em vez do documento JSON para começar. Menos armazenamento, acesso mais rápido e muito mais. É trivialmente simples e rápido adicionar uma chave de volta ao documento JSON na recuperação - ou gerar todo o documento JSON a partir de colunas simples do Postgres.

    Apenas extraindo o ID do Google para uma demonstração:

    ALTER TABLE events
ADD column google_id text;

ALTER TABLE events
  ALTER column google_id TYPE text USING data->>'GoogleId'
, ALTER column data TYPE jsonb USING data - 'GoogleId'
, ALTER column google_id SET NOT NULL;

    O ALTER TABLE"truque" é a maneira mais rápida de reescrever a tabela inteira, mas bloqueia gravações simultâneas. (Eu realmente recriaria a tabela inteira com otimizações adicionais.)

    Agora, o índice pode ser:

    CREATE INDEX events_special2_idx ON events (google_id) INCLUDE (eventable_id)
WHERE  type = 'GoogleCalendarEvent'
AND    created_at >= '2024-08-20+0';

    Supondo que você só precise de eventable_id, adicionei isso com uma INCLUDEcláusula para torná-lo um índice de cobertura. Agora, se a tabela for aspirada o suficiente, você obtém varreduras somente de índice :

    SELECT eventable_id
FROM   events
WHERE  type = 'GoogleCalendarEvent'
AND    google_id = 'abcdef1234'
AND    created_at >= '2024-08-20+0'
LIMIT  1;

    violino

    Armazenar as longas strings 'GoogleCalendarEvent' / 'GoogleEmailEvent' como tipo repetidamente é um desperdício. Eu substituiria isso por algo mais eficiente. Etc.
    Os tipos de dados e o layout da tabela também podem ser otimizados um pouco mais. Veja:

    • 0

relate perguntas