Optimize view (and underlying table) for averaging timestamps into hours
Simpler view

For this objective:


  The point of which is to give averages of the numeric columns for each hour


.. it seems just as good to truncate to full hours, which is simpler and cheaper:

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;


Faster query

Either way, an equivalent query on the view with sargable predicates would be:

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


This is faster, but still not as fast as it could be. The major performance hit is because the index can only be used with an index condition on node_id, which is preserved in its original state in the view. That's why your index rtprices_node_id_interval_idx with node_id first is important. Why?


Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?


The second predicate on hour has to be filtered after the tuple has been fetched from the heap (the row has been read from the table). The large majority of rows is discarded late into the process, much work for nothing.

Much faster with direct query

It would be much faster to run the original query and apply predicates before you aggregate:

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" &gt;= '2015-02-02 0:0'::timestamp
AND    "interval" &lt;  '2015-02-03 0:0'::timestamp
GROUP  BY 1, 2;


You'll see index conditions for all predicates now. The more efficient index is still the one with node_id first. Why?


Multicolumn index and performance


Fast &amp; short: create a function

So, this won't work nicely with a view. Use a function instead:

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" &gt;= _from
AND    r."interval" &lt;  COALESCE(_to, _from + interval '1 day')
GROUP  BY 1, 2
$func$  LANGUAGE sql STABLE;



Be wary of naming conflicts between OUT parameters and column names. That's why I table-qualified all columns here.


Now you get top performance with a simple query:

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


I put in a convenience feature, the second parameter defaults to "one day later" if you omit it:

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


More about function parameters and default values:


Add function parameter to SQL query WHERE clause


You can query any range:

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