Nope UNION isn’t usable…
(pepperstone_live_EURUSD WHERE timestamp > 1577836800000000 AND timestamp < 1695859200000000)
UNION
(pepperstone_live_GBPUSD WHERE timestamp > 1577836800000000 AND timestamp < 1695859200000000)
Those are 3 years of data and query timeout and they aren’t even sorted!
While this query successfully in the range of 1/6sec producing 254+ mln rows
SELECT * FROM pepperstone_live WHERE timestamp > 1577836800000000 AND timestamp < 1695859200000000 AND (symbol = 'EURUSD' OR symbol = 'GBPUSD')
Currently, the pepperstone_live table contains 2.1 billions data… Few seconds are acceptable for me.
I’m executing directly on the VPS, so I’ve 12 vCore and around 40GB of RAM and total usage of quest + my application is around 3.7GB… I’d say, the big table solution seems to be the most effective one.
I’ve only one last question, if I run the query it takes 6/7secs, if I run the same query it takes 3/400ms… I guess Quest internally caches the queries, but after few minutes if I run again the same query it takes again 6/7 sec, and I still guess because this cache have a short ttl. Since my db changes just few times per year and I have a lot of ram, is there a way I can maintain more data in the cache?