This is a followup to: grouping multiple queries into a single one, with Postgres
I have a simple table:
instrument VARCHAR NOT NULL,
ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
quantity FLOAT8 NOT NULL,
price FLOAT8 NOT NULL,
direction INTEGER NOT NULL
and the following query:
SELECT instrument, ts, price, quantity, direction FROM binance.trades t
JOIN (VALUES
('BTCUSDT','2022-03-16 12:00:00'::timestamp),
('ETHUSDT','2022-03-16 12:00:00'::timestamp),
('BCHUSDT','2022-03-16 12:00:00'::timestamp),
('XRPUSDT','2022-03-16 12:00:00'::timestamp),
('EOSUSDT','2022-03-16 12:00:00'::timestamp),
('LTCUSDT','2022-03-16 12:00:00'::timestamp),
('TRXUSDT','2022-03-16 12:00:00'::timestamp),
('ETCUSDT','2022-03-16 12:00:00'::timestamp),
('LINKUSDT','2022-03-16 12:00:00'::timestamp),
('XLMUSDT','2022-03-16 12:00:00'::timestamp),
('ADAUSDT','2022-03-16 12:00:00'::timestamp),
('XMRUSDT','2022-03-16 12:00:00'::timestamp),
('XTZUSDT','2022-03-16 12:00:00'::timestamp),
('BNBUSDT','2022-03-16 12:00:00'::timestamp),
('ATOMUSDT','2022-03-16 12:00:00'::timestamp),
('VETUSDT','2022-03-16 12:00:00'::timestamp),
('THETAUSDT','2022-03-16 12:00:00'::timestamp),
('ALGOUSDT','2022-03-16 12:00:00'::timestamp),
('DOGEUSDT','2022-03-16 12:00:00'::timestamp),
('SNXUSDT','2022-03-16 12:00:00'::timestamp),
('DOTUSDT','2022-03-16 12:00:00'::timestamp),
('RUNEUSDT','2022-03-16 12:00:00'::timestamp),
('SUSHIUSDT','2022-03-16 12:00:00'::timestamp),
('EGLDUSDT','2022-03-16 12:00:00'::timestamp),
('SOLUSDT','2022-03-16 12:00:00'::timestamp),
('UNIUSDT','2022-03-16 12:00:00'::timestamp),
('AVAXUSDT','2022-03-16 12:00:00'::timestamp),
('LUNAUSDT','2022-03-16 12:00:00'::timestamp)
) AS params(i, f)
ON t.instrument = params.i AND t.ts >= params.f
WHERE ts < '2022-03-16 12:01:00'
ORDER BY instrument, ts;
When I execute this query, I get this:
Merge Join (cost=1.59..1010782.11 rows=4416888 width=36) (actual time=450.791..8926.179 rows=4971 loops=1)
" Output: t.instrument, t.ts, t.price, t.quantity, t.direction"
" Merge Cond: ((t.instrument)::text = ""*VALUES*"".column1)"
" Join Filter: (t.ts >= ""*VALUES*"".column2)"
Rows Removed by Join Filter: 15041834
-> Index Scan using idx_trades_instrument_ts on binance.trades t (cost=0.56..732753.74 rows=18456284 width=36) (actual time=0.014..4838.303 rows=17976183 loops=1)
" Output: t.instrument, t.ts, t.quantity, t.price, t.direction"
Index Cond: (t.ts < '2022-03-16 12:01:00'::timestamp without time zone)
-> Sort (cost=1.02..1.09 rows=28 width=40) (actual time=0.032..708.933 rows=14950420 loops=1)
" Output: ""*VALUES*"".column1, ""*VALUES*"".column2"
" Sort Key: ""*VALUES*"".column1"
Sort Method: quicksort Memory: 26kB
" -> Values Scan on ""*VALUES*"" (cost=0.00..0.35 rows=28 width=40) (actual time=0.002..0.009 rows=28 loops=1)"
" Output: ""*VALUES*"".column1, ""*VALUES*"".column2"
Query Identifier: -4392899169609901608
Planning Time: 0.486 ms
JIT:
Functions: 11
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 4.170 ms, Inlining 2.702 ms, Optimization 36.359 ms, Emission 20.524 ms, Total 63.754 ms"
Execution Time: 8930.599 ms
However, if I remove a single instrument from the query, I get that (almost the smae number of rows in the output)
Sort (cost=980284.81..990936.32 rows=4260601 width=36) (actual time=81.722..81.907 rows=4907 loops=1)
" Output: t.instrument, t.ts, t.price, t.quantity, t.direction"
" Sort Key: t.instrument, t.ts"
Sort Method: quicksort Memory: 576kB
-> Nested Loop (cost=0.56..377992.71 rows=4260601 width=36) (actual time=76.345..78.216 rows=4907 loops=1)
" Output: t.instrument, t.ts, t.price, t.quantity, t.direction"
" -> Values Scan on ""*VALUES*"" (cost=0.00..0.34 rows=27 width=40) (actual time=0.005..0.019 rows=27 loops=1)"
" Output: ""*VALUES*"".column1, ""*VALUES*"".column2"
-> Index Scan using idx_trades_instrument_ts on binance.trades t (cost=0.56..12421.72 rows=157800 width=36) (actual time=0.010..0.052 rows=182 loops=27)
" Output: t.instrument, t.ts, t.quantity, t.price, t.direction"
" Index Cond: (((t.instrument)::text = ""*VALUES*"".column1) AND (t.ts >= ""*VALUES*"".column2) AND (t.ts < '2022-03-16 12:01:00'::timestamp without time zone))"
Query Identifier: 8658371483566561811
Planning Time: 0.501 ms
JIT:
Functions: 6
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 4.031 ms, Inlining 32.303 ms, Optimization 30.705 ms, Emission 13.192 ms, Total 80.232 ms"
Execution Time: 86.182 ms
Between 27 and 28 instruments, with almost the same number of rows in the output there is a 100x slowdown.
I'm not experienced enough with SQL to understand the analyzer's output, but I can clearly see that one more entry in the list is changing what's happening.
What could be the reason?
Here is the details with 3x 't.instrument = params.i'
Merge Join (cost=1.59..1390428.62 rows=6957315 width=36) (actual time=797.882..13886.750 rows=6443 loops=1)
" Output: t.instrument, t.ts, t.price, t.quantity, t.direction"
" Merge Cond: ((t.instrument)::text = ""*VALUES*"".column1)"
" Join Filter: (t.ts >= ""*VALUES*"".column2)"
Rows Removed by Join Filter: 23064176
-> Index Scan using idx_trades_instrument_ts on binance.trades t (cost=0.56..952489.47 rows=29071637 width=36) (actual time=0.043..7520.115 rows=28524631 loops=1)
" Output: t.instrument, t.ts, t.quantity, t.price, t.direction"
Index Cond: (t.ts < '2022-03-17 12:01:00'::timestamp without time zone)
-> Sort (cost=1.02..1.09 rows=28 width=40) (actual time=0.032..1094.202 rows=22928388 loops=1)
" Output: ""*VALUES*"".column1, ""*VALUES*"".column2"
" Sort Key: ""*VALUES*"".column1"
Sort Method: quicksort Memory: 26kB
" -> Values Scan on ""*VALUES*"" (cost=0.00..0.35 rows=28 width=40) (actual time=0.003..0.010 rows=28 loops=1)"
" Output: ""*VALUES*"".column1, ""*VALUES*"".column2"
Query Identifier: 7068233849071501838
Planning Time: 0.524 ms
JIT:
Functions: 11
" Options: Inlining true, Optimization true, Expressions true, Deforming true"
" Timing: Generation 4.546 ms, Inlining 13.998 ms, Optimization 59.605 ms, Emission 20.946 ms, Total 99.094 ms"
Execution Time: 13891.608 ms