I have a table with ~300M rows, and perform a simple select over it
SELECT serialno from me_0;
This query is blazing fast, taking 0.156s with nothing cached.
So then I split the data up into five chunks with ~60M rows each, and perform the conceptually equivalent query:
SELECT serialno FROM(
(SELECT serialno FROM me_0) UNION ALL
(SELECT serialno FROM me_1) UNION ALL
(SELECT serialno FROM me_2) UNION ALL
(SELECT serialno FROM me_3) UNION ALL
(SELECT serialno FROM me_4))x;
This took about 80 seconds. I was thinking that this could be optimized because of the columnar nature of IB (since conceptually you’re just appending columns to each other) but I can see from the execution log that this results in the creation of temporary tables.
My question is, for queries where the result set will be extremely large, is there a way to make queries over unions of identical schema tables as fast as “one big table”, or will “one big table” always be faster?