Otimize a visualização (e a tabela subjacente) para obter a média de carimbos de data/hora em horas

Visualização mais simples

Para este objetivo:

O objetivo é fornecer médias das colunas numéricas para cada hora

.. parece tão bom truncar para horas completas, que é mais simples e barato:

CREATE OR REPLACE VIEW spp.rtprices_hourly AS 
SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
GROUP  BY 1, 2;

consulta mais rápida

De qualquer forma, uma consulta equivalente na exibição com predicados sargáveis ​​seria:

SELECT *
FROM   spp.rtprices_hourly
WHERE  node_id = 20
AND    hour >= '2015-02-02 0:0'::timestamp
AND    hour <  '2015-02-03 0:0'::timestamp;

Isso é mais rápido, mas ainda não tão rápido quanto poderia ser. O maior impacto no desempenho ocorre porque o índice só pode ser usado com uma condição de índice em node_id, que é preservada em seu estado original na exibição. É por isso que seu índice rtprices_node_id_interval_idxcom o node_idprimeiro é importante. Por quê?

• Funcionamento de índices no PostgreSQL
• Um índice composto também é bom para consultas no primeiro campo?

O segundo predicado hourdeve ser filtrado depois que a tupla foi buscada no heap (a linha foi lida da tabela). A grande maioria das linhas é descartada no final do processo, muito trabalho para nada .

Muito mais rápido com consulta direta

Seria muito mais rápido executar a consulta original e aplicar predicados antes de agregar:

SELECT date_trunc('hour', "interval") AS hour
     , node_id
     , round(avg(rtlmp), 2) AS rtlmp
     , round(avg(rtmcc), 2) AS rtmcc
     , round(avg(rtmcl), 2) AS rtmcl
FROM   spp.rtprices
WHERE  node_id = 20
AND    "interval" >= '2015-02-02 0:0'::timestamp
AND    "interval" <  '2015-02-03 0:0'::timestamp
GROUP  BY 1, 2;

Você verá as condições de índice para todos os predicados agora. O índice mais eficiente ainda é aquele com o node_idprimeiro. Por quê?

• Índice e desempenho de várias colunas

Rápido e curto: crie uma função

Portanto, isso não funcionará bem com uma exibição. Em vez disso, use uma função:

CREATE OR REPLACE FUNCTION rtprices_hourly(_node_id int
                                         , _from timestamp
                                         , _to timestamp = NULL)
  RETURNS TABLE (
    hour    timestamp
  , node_id int
  , rtlmp   numeric
  , rtmcc   numeric
  , rtmcl   numeric) AS
$func$
SELECT date_trunc('hour', r."interval")  -- AS hour
     , r.node_id
     , round(avg(r.rtlmp), 2)  -- AS rtlmp
     , round(avg(r.rtmcc), 2)  -- AS rtmcc
     , round(avg(r.rtmcl), 2)  -- AS rtmcl
FROM   spp.rtprices r
WHERE  r.node_id     = _node_id
AND    r."interval" >= _from
AND    r."interval" <  COALESCE(_to, _from + interval '1 day')
GROUP  BY 1, 2
$func$  LANGUAGE sql STABLE;

• Tenha cuidado ao nomear conflitos entre parâmetros OUT e nomes de colunas. É por isso que qualifiquei todas as colunas aqui.

Agora você obtém o melhor desempenho com uma consulta simples:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp, '2015-2-3 0:0'::timestamp);

Eu coloquei um recurso de conveniência, o segundo parâmetro é padronizado como "um dia depois" se você omitir:

SELECT * FROM rtprices_hourly(1, '2015-2-2 0:0'::timestamp);

Mais sobre parâmetros de função e valores padrão:

• Adicionar parâmetro de função à cláusula WHERE da consulta SQL

Você pode consultar qualquer intervalo:

SELECT * FROM rtprices_hourly(1, '2015-2-2 10:0'::timestamp, '2015-2-2 20:0'::timestamp);