Início / dba / Perguntas / 110460
Accepted
NumberFour
Asked: 2015-08-09 08:14:43 +0800 CST

Por que a cláusula WHERE não é empurrada para baixo na consulta da exibição?

  • 772

Com o Postgres 9.4, estou fazendo a seguinte consulta com bastante frequência:

SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

Então resolvi criar uma view:

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
ORDER BY recipient, timestamp DESC;

Acabei de perceber que, se consultar minha visão SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB, obterei um desempenho significativamente pior.

Fazendo EXPLAIN ANALYZEem ambas as consultas, descobri que o motivo é que o banco de dados no segundo caso traz todos os registros, faz a junção à esquerda e aplica a WHEREcláusula. Em outras palavras, a WHEREcláusula não é inserida na consulta da exibição. Também tentei remover o ORDER BYda exibição, mas ainda assim o banco de dados executa os LEFT JOINdados completos em vez do conjunto filtrado.

Qual é a razão deste comportamento? Existe uma maneira de obter um desempenho comparável ao usar a visualização?

postgresql performance

2 respostas

  1. Esta consulta:

    SELECT DISTINCT ON(recipient) * FROM messages
LEFT JOIN identities ON messages.recipient = identities.name
WHERE messages.timestamp BETWEEN timeA AND timeB
ORDER BY recipient, timestamp DESC;

    diz:

    Para todas as mensagens entre timeA e timeB , encontre os destinatários e para cada destinatário, encontre uma mensagem (a última entre timeA e timeB).

    Esta consulta (que você obteria se usasse a visualização):

    SELECT *
FROM 
  ( SELECT DISTINCT ON(recipient) * FROM messages
    LEFT JOIN identities ON messages.recipient = identities.name
    ORDER BY recipient, timestamp DESC
  ) AS myView
WHERE timestamp BETWEEN timeA AND timeB;

    diz:

    Para todas as mensagens , encontre os destinatários e, para cada destinatário, encontre uma mensagem (a mais recente de todos os tempos) e, em seguida, mostre apenas as mensagens que estão entre o horário A e o horário B.

    Como resultado, a primeira consulta mostrará as mensagens que estão entre o horário A e B e a segunda consulta não mostrará (porque pode haver uma ou mais mensagens para o mesmo destinatário, posteriores ao horário B).

    Portanto, as consultas são logicamente diferentes e a condição não pode (e não deve) ser pressionada para sua exibição.

    Se você deseja que os parâmetros sejam passados ​​para sua exibição, consulte as duas respostas (por @a_horse_with_no_name e @Erwin Brandstetter ) nesta pergunta para saber como usar uma função de retorno definida : Passar os parâmetros “WHERE” para a exibição do PostgreSQL?

    • 13
  2. Best Answer

    Você pode criar uma função como esta;

    CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp (
  time_a timestamp,
  time_b timestamp
)
RETURNS TABLE (
  recipient varchar,
  "timestamp" timestamp
) AS
$$
BEGIN
  RETURN QUERY
    SELECT DISTINCT ON (m.recipient) 
        m.recipient,
        m."timestamp"
      FROM messages m
      LEFT JOIN identities i ON m.recipient = i.name
      WHERE 
        m."timestamp" BETWEEN time_a AND time_b
      ORDER BY 
        m.recipient,
        m."timestamp" DESC;
END;
$$
LANGUAGE 'plpgsql';

    Então você pode usar a função como uma tabela

      SELECT *
         FROM get_messages_by_timestamp('2015-01-01', '2015-01-02')
    • 7

relate perguntas