Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE binance_trades (ts timestamp without time zone, val int, price numeric,quantity int, direction int,instrument text);

INSERT INTO binance_trades
SELECT ts,round(random()*10),random()*42,round(random()*8),round(random()*5), upper(substr(md5(random()::text), 0, 7))
FROM generate_series('2021-08-19 23:58:42','2021-08-20 01:01:00',interval'1 sec') j(ts);

CREATE INDEX idx_binance_trades_ts_instrument ON binance_trades USING btree (ts);
3739 rows affected

EXPLAIN (ANALYSE,COSTS OFF)
SELECT
  date_trunc('second',ts)-
    MOD(EXTRACT(SECOND FROM date_trunc('second',ts))::int,5)*interval'1 sec' AS ts_5sec,
  MAX(price) high,
  MIN(price) low,
  (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
  SUM(price * quantity) volume,
  (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM binance_trades
WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'
GROUP BY 1 ORDER BY 1
LIMIT 5;


QUERY PLAN


Limit (actual time=4.344..4.389 rows=5 loops=1)

  ->  GroupAggregate (actual time=4.342..4.386 rows=5 loops=1)

        Group Key: ((date_trunc('second'::text, ts) - ((mod((date_part('second'::text, date_trunc('second'::text, ts)))::integer, 5))::double precision * '00:00:01'::interval)))

        ->  Sort (actual time=4.313..4.315 rows=24 loops=1)

              Sort Key: ((date_trunc('second'::text, ts) - ((mod((date_part('second'::text, date_trunc('second'::text, ts)))::integer, 5))::double precision * '00:00:01'::interval)))

              Sort Method: quicksort  Memory: 384kB

              ->  Bitmap Heap Scan on binance_trades (actual time=0.392..3.048 rows=3679 loops=1)

                    Recheck Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

                    Heap Blocks: exact=35

                    ->  Bitmap Index Scan on idx_binance_trades_ts_instrument (actual time=0.357..0.357 rows=3679 loops=1)

                          Index Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

Planning Time: 0.509 ms

Execution Time: 4.583 ms


EXPLAIN (ANALYSE,COSTS OFF)
WITH j AS (
  SELECT i AS win_start,tsrange(i,i+interval'5sec') AS ts_win
  FROM generate_series(
    (SELECT min(date_trunc('second',ts)) FROM binance_trades
     WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'),
    (SELECT max(date_trunc('second',ts)) FROM binance_trades
     WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'),interval'5 sec') j (i))
SELECT
    j.win_start ts,
    MAX(price) high,
    MIN(price) low,
    (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
    SUM(price * quantity) volume,
    (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM j
JOIN binance_trades b ON ts_win @> b.ts
GROUP BY j.win_start ORDER BY j.win_start
LIMIT 5;


QUERY PLAN


Limit (actual time=812.097..812.146 rows=5 loops=1)

  InitPlan 1 (returns $0)

    ->  Aggregate (actual time=1.592..1.593 rows=1 loops=1)

          ->  Bitmap Heap Scan on binance_trades (actual time=0.240..0.689 rows=3679 loops=1)

                Recheck Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

                Heap Blocks: exact=35

                ->  Bitmap Index Scan on idx_binance_trades_ts_instrument (actual time=0.228..0.228 rows=3679 loops=1)

                      Index Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

  InitPlan 2 (returns $1)

    ->  Aggregate (actual time=1.469..1.470 rows=1 loops=1)

          ->  Bitmap Heap Scan on binance_trades binance_trades_1 (actual time=0.169..0.599 rows=3679 loops=1)

                Recheck Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

                Heap Blocks: exact=35

                ->  Bitmap Index Scan on idx_binance_trades_ts_instrument (actual time=0.162..0.162 rows=3679 loops=1)

                      Index Cond: ((ts >= '2021-08-19 22:50:00'::timestamp without time zone) AND (ts <= '2021-08-20 01:00:00'::timestamp without time zone))

  ->  GroupAggregate (actual time=812.095..812.140 rows=5 loops=1)

        Group Key: j.i

        ->  Sort (actual time=812.065..812.067 rows=26 loops=1)

              Sort Key: j.i

              Sort Method: quicksort  Memory: 384kB

              ->  Nested Loop (actual time=3.227..810.030 rows=3680 loops=1)

                    Join Filter: (tsrange(j.i, (j.i + '00:00:05'::interval)) @> b.ts)

                    Rows Removed by Join Filter: 2748224

                    ->  Function Scan on generate_series j (actual time=3.160..3.492 rows=736 loops=1)

                    ->  Materialize (actual time=0.000..0.297 rows=3739 loops=736)

                          ->  Seq Scan on binance_trades b (actual time=0.012..0.803 rows=3739 loops=1)

Planning Time: 0.524 ms

Execution Time: 812.322 ms


CREATE UNLOGGED TABLE scalper_5s_intervals AS
SELECT i AS win_start,tsrange(i,i+interval'5sec') AS ts_win
FROM generate_series(
  (SELECT min(date_trunc('second',ts)) FROM binance_trades
   WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'),
  (SELECT max(date_trunc('second',ts)) FROM binance_trades
   WHERE ts BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'),interval'5 sec') j (i);

CREATE INDEX idx_ts_5sec ON scalper_5s_intervals USING gist (ts_win);
CREATE INDEX idx_ts_5sec_winstart ON scalper_5s_intervals USING btree(win_start);

EXPLAIN (ANALYSE,COSTS OFF)  
SELECT
    j.win_start ts,
    MAX(price) high,
    MIN(price) low,
    (SUM(price * price * quantity) / SUM(price * quantity)) midpoint,
    SUM(price * quantity) volume,
    (SUM(direction * price * quantity) / SUM(price * quantity)) direction
FROM scalper_5s_intervals j
JOIN binance_trades b ON ts_win @> b.ts
WHERE j.win_start BETWEEN '2021-08-19 22:50:00' AND '2021-08-20 01:00:00'
GROUP BY j.win_start ORDER BY j.win_start
LIMIT 5; 
736 rows affected



QUERY PLAN


Limit (actual time=506.751..506.798 rows=5 loops=1)

  ->  GroupAggregate (actual time=506.749..506.795 rows=5 loops=1)

        Group Key: j.win_start

        ->  Sort (actual time=506.717..506.720 rows=26 loops=1)

              Sort Key: j.win_start

              Sort Method: quicksort  Memory: 384kB

              ->  Nested Loop (actual time=0.169..504.919 rows=3680 loops=1)

                    Join Filter: (j.ts_win @> b.ts)

                    Rows Removed by Join Filter: 2748224

                    ->  Seq Scan on binance_trades b (actual time=0.015..0.744 rows=3739 loops=1)

                    ->  Materialize (actual time=0.000..0.037 rows=736 loops=3739)

                          ->  Bitmap Heap Scan on scalper_5s_intervals j (actual time=0.143..0.267 rows=736 loops=1)

                                Recheck Cond: ((win_start >= '2021-08-19 22:50:00'::timestamp without time zone) AND (win_start <= '2021-08-20 01:00:00'::timestamp without time zone))

                                Heap Blocks: exact=6

                                ->  Bitmap Index Scan on idx_ts_5sec_winstart (actual time=0.129..0.129 rows=736 loops=1)

                                      Index Cond: ((win_start >= '2021-08-19 22:50:00'::timestamp without time zone) AND (win_start <= '2021-08-20 01:00:00'::timestamp without time zone))

Planning Time: 0.384 ms

Execution Time: 506.972 ms