Resumo

Tenho uma tabela com fontes de eventos e uma tabela com eventos. Preciso de uma consulta que me forneça os Neventos mais recentes de cada fonte ( Nestá entre 1 e 100). Atualmente, estou fazendo isso com uma subconsulta que executa uma ROW_NUMBER() OVER (PARTITION BY "EventSourceId" ORDER BY ...) as rankRecentconsulta externa que filtra WHERE rankRecent <= @N.

Os EXPLAIN ANALYZEresultados dizem que ele está usando meu índice para as cláusulas partitione order by, mas ainda está classificando a tabela inteira e aparentemente esperando encontrar 6 milhões de resultados, mas existem apenas 22 mil. Estou tentando descobrir se existe: (1) uma maneira melhor de obter os Neventos mais recentes para cada fonte de evento ou (2) uma maneira de sugerir ao planejador de consultas que ele não precisa classificar estritamente a maioria dos tabela, pois apenas as primeiras entradas serão usadas.

Além disso, há um segundo caso de uso para a consulta que nem sei como começar a indexar. Esse não é o cerne desta questão; Menciono-o apenas no interesse de incluir tudo o que possa ser relevante.

Detalhes

Configuração de dados

CREATE TABLE "EventSources"
(
    "Id" uuid NOT NULL,
    "Name" character varying(100),
    CONSTRAINT "PK_EventSources" PRIMARY KEY ("Id")
);

CREATE TABLE "Events"
(
    "Id" uuid NOT NULL,
    "EventSourceId" uuid NOT NULL,
    "Time" timestamp with time zone,
    "AltKey" character varying(100),
    CONSTRAINT "PK_Events" PRIMARY KEY ("Id"),
    CONSTRAINT "FK_Events_EventSources_EventSourceId" FOREIGN KEY ("EventSourceId") REFERENCES "EventSources" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT
);

CREATE INDEX "IX_Events_EventSourceId_Time_Desc_AltKey_Desc" ON "Events" USING btree
(
    "EventSourceId" ASC,
    "Time" DESC NULLS LAST,
    "AltKey" DESC NULLS LAST
);

Algumas informações adicionais possivelmente relevantes:

SELECT version(); --PostgreSQL 13.12, compiled by Visual C++ build 1914, 64-bit
SELECT COUNT(*) FROM "EventSources"; --29,000ish
SELECT COUNT(*) FROM "Events"; --20,000,000ish
SELECT COUNT(*) FROM (SELECT DISTINCT "EventSourceId" FROM "Events") sub; --5,000ish. Most of the "EventSources" don't have "Events" but are used for other things in the db

A pergunta

Aqui está a consulta que estou tentando otimizar:

SELECT
    *
FROM
    (
        SELECT
            "Events".*,
            ROW_NUMBER() OVER (
                PARTITION BY "Events"."EventSourceId"
                ORDER BY
                    "Events"."Time" DESC NULLS LAST,
                    "Events"."AltKey" DESC NULLS LAST
            ) as rankRecent
        FROM
            "Events"
        --WHERE "Events"."Time" < @LimitTime
    ) sub
WHERE
    rankRecent <= @N; -- @N is in the range 1 to 100.

Casos de uso

Aqui estão os casos de uso para a consulta:

1. Estou carregando um painel que exibe cálculos agregados de dados recentes para cada fonte de evento e, com base nesses cálculos, escolhe quais fontes de eventos serão exibidas e em qual ordem.
2. Estou investigando um problema que aconteceu ontem às 3h14min15s da manhã e, para uma determinada coleção de fontes de eventos relevantes, preciso ver os 100 eventos que levaram a esse horário para poder ver o que pode ter acontecido. estava dando errado. Neste caso, a WHEREcláusula comentada na consulta será descomentada, podendo também se unir a outra tabela para filtrar fontes de eventos relacionadas a um determinado contexto. Não tenho ideia de como indexar isso, mas esse não é o cerne da questão.

Explicar

Aqui está o resultado de EXPLAIN (ANALYZE, BUFFERS, SETTINGS)(neste caso, @Nfoi definido como 5):

• Verificação de subconsulta em sub (custo = 0,56..2637123,43 linhas = 6664000 largura = 66) (tempo real = 0,156..90245,642 linhas = 22613 loops = 1)
  • Filtro: (sub.rankrecent <= 5)
  • Linhas removidas pelo filtro: 19963368
  • Buffers: hit compartilhado=6738934 lido=13332278
  • -> WindowAgg (custo = 0,56..2387223,43 linhas = 19992000 largura = 66) (tempo real = 0,155..89355,268 linhas = 19985981 loops = 1)
    • Buffers: hit compartilhado=6738934 lido=13332278
      • -> Varredura de índice usando "IX_Events_EventSourceId_Time_Desc_AltKey_Desc" em "Eventos" (custo = 0,56..1987383,43 linhas = 19992000 largura = 58) (tempo real = 0,100..82274,745 linhas = 19985981 loops = 1)
        • Buffers: hit compartilhado=6738934 lido=13332278
• Tempo de planejamento: 0,111 ms
• Tempo de execução: 90247,357ms

Abordagens alternativas que considerei

1. Considerei usar uma visão materializada para fazer o trabalho pesado de antemão. No entanto, a "Events"tabela terá dados inseridos cerca de uma dúzia de vezes por segundo, e cada transação pode conter de zero a algumas dezenas de linhas. Portanto, se a visualização usar o mesmo plano de consulta que a consulta existente, os dados na visualização ficarão totalmente desatualizados antes que um comando para atualizar a visualização possa ser concluído. Então concluí que não fazia sentido essa estratégia.
2. Também considerei tentar adicionar uma nova sequencecoluna inteira na "Events"tabela e depois filtrar nessa sequência. Mas isso realmente não resolve o problema. Cada transação que insere eventos pode ou não ter eventos para uma determinada fonte de eventos, portanto, os 5 eventos mais recentes para uma determinada fonte de eventos podem incluir dados de, por exemplo, uma transação de 5 segundos atrás e uma transação de uma hora atrás. , e um de ontem e um da semana passada e um do ano passado. Portanto, não há uma maneira fácil de sincronizar isso sequenceentre milhares de fontes de eventos diferentes. Além disso, é possível que uma fonte de eventos envie manualmente dados que foram perdidos em um momento arbitrário no passado, portanto, a classificação "Time"parece estar correta.
3. Considerei ter uma tabela que monitorasse apenas, digamos, os 50 eventos mais recentes para cada fonte de evento e descartasse qualquer coisa mais antiga. Dessa forma, a consulta poderia prosseguir e classificar a tabela inteira, pois não haveria tantos dados para classificar. No entanto, a consulta não satisfaria mais o Caso de Uso nº 2; e, embora o caso de uso nº 2 não seja o objetivo desta questão, não posso simplesmente removê-lo da consulta sem fornecer um substituto.

Esclarecimento

A row_number over partitionquestão é a questão principal aqui. O material sobre o Caso de Uso nº 2 e não saber como indexá-lo está incluído aqui, caso inspire alguém a realizar um plano, consulta ou esquema melhor; Não estou esperando uma solução para isso, apenas quero incluir qualquer informação que possa ser relevante.