Writing a view that caches to another table
CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS SETOF numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   RETURN QUERY
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql VOLATILE;


A couple of warnings


It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.
BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.
There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:


Is SELECT or INSERT in a function prone to race conditions?



Alternative with tsrange

...
AND    ts_range = tsrange(_start_time, _end_time, '[]')
...
   INSERT INTO market_trades_sum_cache(market_id, ts_range, sum)
   SELECT market_id
        , tsrange(_start_time, _end_time, '[]')  -- incl. bounds
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
...


I would consider a multicolumn GIN or GiST index for the range type. Though, if you only check for equality, a btree index would do the job, too. But the latter isn't much use for anything else. Details in the manual.

To include market_id in the index (like I'd suggest), you also need the additional module btree_gin or btree_gist. Then create the index:

CREATE INDEX sum_mult_idx ON market_trades_sum_cache USING GIN (market_id, ts_range, sum);


The last column sum is optional and only makes sense if you get index-only scans out of it. Else don't include it in the index.

Or, if you want to disallow overlapping time ranges, use an exclusion constraint, based on a GiST index (GIN is not currently possibly). Details:


PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
Optimizing queries on a range of timestamps (two columns)
2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL


The last one also provides a clean solution for enforcing '[)' boundaries.