Início / dba / Perguntas / 55945
Accepted
John
Asked: 2014-01-02 14:44:03 +0800 CST

Postgres - Otimizando uma visão dependente de uma função agregada?

  • 772

Eu tenho uma tabela de cerca de 15 milhões de registros. Sempre que as informações sobre um charge_id específico são alteradas, uma nova linha é adicionada com o timestamp atual e as alterações. Isso resulta em várias linhas com o mesmo charge_id e colunas hierárquicas relacionadas. Isso não é controlado por mim e não pode ser alterado, além disso, gostamos de ter o histórico disponível para consulta.

A exibição abaixo destina-se a identificar a entrada mais recente para cada charge_id e criar uma tabela simples de par de chave-valor para junção. A visualização funciona bem, mas o tempo de execução é horrível. Eu tentei algumas tentativas em um índice para acelerar as coisas, mas cada vez parece que o postgres está ignorando o índice e verificando tudo de qualquer maneira. Também devo observar que a maioria das consultas em que nos unimos a essa exibição serão agregações de tabela completa, agrupadas por 2 a 5 dimensões diferentes na tabela de cobranças.

Minha pergunta especificamente é o que posso fazer para acelerar o tempo de execução nessa exibição específica?

CREATE VIEW current_charge_ids AS 
(
SELECT
    c2.id,
    c2.charge_id,
    t1.last_post_date
FROM
    charges c2
LEFT JOIN
    (
        SELECT
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id,
            max(c1.post_date) AS last_post_date
        FROM
            charges c1
        GROUP BY
            c1.client,
            c1.practice,
            c1.account_id,
            c1.encounter_id,
            c1.charge_id
    ) t1
ON
    c2.client = t1.client AND
    c2.practice = t1.practice AND
    c2.account_id = t1.account_id AND
    c2.encounter_id = t1.encounter_id AND
    c2.charge_id = t1.charge_id AND
    c2.post_date = t1.last_post_date
);
postgresql index

1 respostas

  1. Best Answer

    Você pode simplificar enormemente a consulta usando DISTINCT ON:

    SELECT DISTINCT ON (client, practice, account_id, encounter_id, charge_id)
       id, charge_id, post_date
FROM   charges
ORDER  BY client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC;

    Será consideravelmente mais rápido em qualquer caso. Explicação detalhada nesta resposta relacionada em SO:
    Selecione a primeira linha em cada grupo GROUP BY?

    A última ORDER BYexpressão id DESCé opcional para desempatar se resto ainda não deve ser inequívoco. Pode não ser necessário.

    Apoie isso com um índice de várias colunas correspondente :

    CREATE INDEX charges_latest_idx ON charges
(client, practice, account_id, encounter_id, charge_id, post_date DESC, id DESC);

    Se tal índice será útil depende de detalhes não revelados.

    Observe em particular que a ordem de classificação deve corresponder à consulta. No Postgres 9.2 ou posterior, isso pode até funcionar como índice de cobertura , dependendo de detalhes não revelados.

    Dependendo de detalhes não revelados, uma visão materializada também pode ser uma candidata. Quanto mais operações de gravação, menor a probabilidade de isso ajudar. O mesmo vale para o índice de cobertura.

    • 3

relate perguntas