Skip to content

Instantly share code, notes, and snippets.

@nalzok

nalzok/benchmark.sql

Last active August 5, 2020 14:15
Show Gist options
  • Save nalzok/174c2fe365fb8729a4392aef63348fe0 to your computer and use it in GitHub Desktop.
Save nalzok/174c2fe365fb8729a4392aef63348fe0 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
benchmark.sql
------------
-- Set up --
------------
.print ''
.print '[Set up]'
.open bmk.db
DROP TABLE IF EXISTS candidates;
DROP TABLE IF EXISTS results;
CREATE TABLE candidates AS
WITH RECURSIVE candidates(team, score) AS (
SELECT ABS(RANDOM()) % 1000, 1
UNION
SELECT ABS(RANDOM()) % 1000, score + 1
FROM candidates
LIMIT 1000000
)
SELECT team, score
FROM candidates;
VACUUM;
ANALYZE;
-------------------
-- Without Index --
-------------------
.print ''
.print '[Without Index]'
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
VACUUM;
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
VACUUM;
------------------------------
-- With Single Column Index --
------------------------------
.print ''
.print '[With Single Column Index]'
CREATE INDEX candidates_idx_1 ON candidates(team);
ANALYZE;
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
VACUUM;
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
VACUUM;
-----------------------------
-- With Multi Column Index --
-----------------------------
.print ''
.print '[With Multi Column Index]'
CREATE INDEX candidates_idx_2 ON candidates(team, score);
ANALYZE;
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
VACUUM;
.timer ON
.eqp ON
.echo ON
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
.echo OFF
.eqp OFF
.timer OFF
.sha3sum results
DROP TABLE results;
DROP TABLE candidates;
VACUUM;
Raw
linux-output.txt
[Set up]
[Without Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 456.715 user 437.129147 sys 19.484501
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.936 user 0.885235 sys 0.047963
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
[With Single Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 2336.369 user 932.471334 sys 1403.677723
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 4.614 user 1.928398 sys 2.683287
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
[With Multi Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 78.189 user 71.831376 sys 6.351158
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.177 user 0.163415 sys 0.011350
.echo OFF
88a6030ff544cbcdaa7f6a9077f4d542e067e46d670777d479923a9d|results
Raw
macos-output.txt
[Set up]
[Without Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 433.502 user 396.997885 sys 33.371485
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.905 user 0.832549 sys 0.065956
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
[With Single Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 1555.703 user 710.638950 sys 839.960920
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 3.250 user 1.452716 sys 1.766816
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
[With Multi Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 77.443 user 72.991242 sys 3.933482
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.160 user 0.149562 sys 0.009031
.echo OFF
a8817f9c0deaf409f0a60e278630d364b94f3d40ce3558a99ece4180|results
Raw
windows-output.txt
[Set up]
[Without Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 480.079 user 462.062500 sys 16.859375
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates
|--USE TEMP B-TREE FOR GROUP BY
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates
| |--USE TEMP B-TREE FOR GROUP BY
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.960 user 0.937500 sys 0.015625
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
[With Single Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 2116.212 user 643.437500 sys 1471.156250
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING INDEX candidates_idx_1
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING INDEX candidates_idx_1
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 4.381 user 1.375000 sys 2.984375
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
[With Multi Column Index]
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
)
SELECT team, SUM(score) * EXISTS(
SELECT 1
FROM top_teams_verbose
WHERE team = top_team
)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--CORRELATED SCALAR SUBQUERY 2
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 90.735 user 84.656250 sys 6.062500
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
CREATE TABLE results AS
WITH top_teams_verbose(top_team, total_score) AS (
SELECT team, SUM(score)
FROM candidates
GROUP BY team
ORDER BY 2 DESC
LIMIT 50
),
top_teams AS (
SELECT top_team
FROM top_teams_verbose
)
SELECT team, SUM(score) * (team IN top_teams)
FROM candidates
GROUP BY team
ORDER BY team;
QUERY PLAN
|--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
`--LIST SUBQUERY 3
|--CO-ROUTINE 1
| |--SCAN TABLE candidates USING COVERING INDEX candidates_idx_2
| `--USE TEMP B-TREE FOR ORDER BY
`--SCAN SUBQUERY 1
Run Time: real 0.199 user 0.187500 sys 0.015625
.echo OFF
97bf5cca5b45ab6b3474410a26b358e7130651feebd5d6462cd595e0|results
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment