Início / dba / Perguntas / 60494
Accepted
JohnMerlino
Asked: 2014-03-09 15:49:42 +0800 CST

otimização de subconsultas caras

  • 772

A consulta a seguir obtém o último relatório (onde latitude, longitude e segundos são <> 0) associado às unidades especificadas:

SELECT 
    reports.* 
FROM
    reports 
    INNER JOIN 
        units 
        ON units.id = reports.unit_id 
WHERE 
    reports.unit_id IN (1111, 1112, 1113) 
    AND 
    (
        reports.id = 
        (
            SELECT reports.id 
            FROM reports
            WHERE reports.unit_id = units.id
            AND
            reports.time_secs != 0
            AND 
            reports.latitude != 0.0
            AND
            reports.longitude != 0.0
            ORDER BY time desc
            LIMIT 1
        )
    )

Essa consulta levou vários minutos para ser executada e gostaria de saber se há uma otimização que eu possa fazer nela.

postgresql performance

2 respostas

  1. Best Answer

    Isso pode ser consideravelmente mais simples e rápido com DISTINCT ON:

    SELECT DISTINCT ON (r.unit_id)
       r.* 
FROM   reports r
JOIN   units   u ON u.id = r.unit_id 
WHERE  r.unit_id IN (1111, 1112, 1113) 
AND    r.time_secs <> 0
AND    r.latitude  <> 0.0
AND    r.longitude <> 0.0
ORDER  BY r.unit_id, r.time DESC;

    Mais explicações nestas respostas relacionadas:
    Como obtenho com eficiência "a linha correspondente mais recente"?
    Selecione a primeira linha em cada grupo GROUP BY? (em SO)

    Detalhe menor: o operador SQL padrão para "não é igual" é <>. Use isso em vez de !=(que também é aceito no Postgres).

    A única finalidade possível de ingressar na tabela unitsnesta consulta é verificar se existe uma ou mais linhas relacionadas. Se reports.unit_idfor vinculado unit.idpor chave estrangeira (como sugere a nomenclatura), a integridade referencial é garantida e você pode eliminar unitcompletamente a tabela da consulta. Basta adicionar: unit_id IS NOT NULL.

    SELECT DISTINCT ON (unit_id) * 
FROM   reports
WHERE  unit_id IN (1111, 1112, 1113) 
AND    time_secs <> 0
AND    latitude  <> 0.0
AND    longitude <> 0.0
AND    unit_id IS NOT NULL
ORDER  BY unit_id, time DESC;
    • 4 
  2.   WITH T1 as (SELECT 
            reports.* , row_number() over (partition by unit_id order by reports.time DESC) as RN
        FROM
            reports 
            INNER JOIN 
                units 
                ON units.id = reports.unit_id
        WHERE 
        reports.unit_id IN (1111, 1112, 1113) AND
        reports.time_secs <> 0 AND 
        reports.latitude <> 0.0 AND
        reports.longitude <> 0.0)
)
    SELECT * FROM T1 WHERE RN = 1
    • 2

relate perguntas