Row Generation 성능 비교 - SingleStore, Oracle, MySQL, PostgreSQL

SQL> select count(*) from (select rownum from dual connect by level <= 100000000); select count(*) from (select rownum from dual connect by level <= 100000000) * ERROR at line 1: ORA-30009: Not enough memory for CONNECT BY operation SQL> select count(*) from (select rownum from dual connect by level <= 2200000); COUNT(*) ---------- 2200000 Elapsed: 00:00:00.54 SQL> with t(n) as ( 2 select 1 as n from dual 3 union all 4 select n+1 as n from t where n < 10000000 5 ) 6 select count(*) from t; COUNT(*) ---------- 10000000 Elapsed: 00:02:07.19

SQL> show parameter pga_aggregate_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 3000M SQL> select count(*) from (select rownum from dual connect by level <= 10000000); COUNT(*) ---------- 10000000 Elapsed: 00:00:03.21 SQL> with t(n) as ( 2 select 1 as n from dual 3 union all 4 select n+1 as n from t where n < 10000000 5 ) 6 select count(*) from t; COUNT(*) ---------- 10000000 Elapsed: 00:01:40.69

mysql> set @@cte_max_recursion_depth=100000000; Query OK, 0 rows affected (0.00 sec) mysql> with recursive t(n) as ( -> select 1 as n -> union all -> select n+1 as n from t where n < 10000000 -> ) -> select count(*) from t; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1.73 sec) mysql> with recursive t(n) as ( -> select 1 as n -> union all -> select n+1 as n from t where n < 100000000 -> ) -> select count(*) from t; +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (1 min 3.85 sec)

postgres=# select count(*) from generate_series(1, 10000000); count ---------- 10000000 (1 row) Time: 1583.327 ms (00:01.583) postgres=# select count(*) from generate_series(1, 100000000); count ----------- 100000000 (1 row) Time: 15474.199 ms (00:15.474) postgres=# with recursive t(n) as ( postgres(# select 1 as n postgres(# union all postgres(# select n+1 as n from t where n < 10000000 postgres(# ) postgres-# select count(*) from t; count ---------- 10000000 (1 row) Time: 4682.084 ms (00:04.682) postgres=# with recursive t(n) as ( postgres(# select 1 as n postgres(# union all postgres(# select n+1 as n from t where n < 100000000 postgres(# ) postgres-# select count(*) from t; count ----------- 100000000 (1 row) Time: 47140.642 ms (00:47.141)

singlestore> select count(*) from gen_rows(100000000); +-----------+ | count(*) | +-----------+ | 100000000 | +-----------+ 1 row in set (0.86 sec) singlestore> select count(*) from gen_rows(200000000); +-----------+ | count(*) | +-----------+ | 200000000 | +-----------+ 1 row in set (1.85 sec) singlestore> select count(*) from gen_rows(300000000); +-----------+ | count(*) | +-----------+ | 300000000 | +-----------+ 1 row in set (2.60 sec) singlestore> select count(*) from gen_rows(400000000); +-----------+ | count(*) | +-----------+ | 400000000 | +-----------+ 1 row in set (4.03 sec) singlestore> select count(*) from gen_rows(500000000); +-----------+ | count(*) | +-----------+ | 500000000 | +-----------+ 1 row in set (4.44 sec) singlestore> select count(*) from gen_rows(600000000); +-----------+ | count(*) | +-----------+ | 600000000 | +-----------+ 1 row in set (6.40 sec) singlestore> select count(*) from gen_rows(700000000); ERROR 1712 (HY000): Memory used by MemSQL (8328.62 Mb) has reached the 'maximum_memory' setting (14141 Mb) on this node. Possible causes include (1) available query execution memory has been used up for table memory (in use table memory: 8058.62 Mb) and (2) the query is large and complex and requires more query execution memory than is available (in use query execution memory 0.00 Mb). See https://docs.memsql.com/troubleshooting/latest/memory-errors for additional information.