sqlbench

sqlbench

sqlbench measures and compares the execution time of one or more SQL queries.

The main use case is benchmarking simple CPU-bound query variants against each other during local development.

Only PostgreSQL is supported at this point, but pull requests for MySQL or other databases are welcome.

Install

You can download a binary from the release page.

If you have Go 1.16 or later installed, you can install or update sqlbench from source:

$ go get -u github.com/felixge/sqlbench

Windows Users: You may also install the chocolatey package maintained by picolino:

$ choco install sqlbench

Install via brew

If you're macOS user and using Homebrew, you can install via brew command:

$ brew update
$ brew install sqlbench

Examples

Below are a few one-liners to get you started. They assume you're running sqlbench from the directory of a clone of this repo.

…

Usage

…

How It Works

sqlbench takes a list of SQL files and keeps executing them sequentially, measuring their execution times. By default the execution time is measured by prefixing the query with EXPLAIN (ANALYZE, TIMING OFF) and capturing the total Execution Time for it.

The query columns are ordered by mean execution time in ascending order, and the relative difference compared to the fastest query is shown in parentheses. If you provide a baseline csv via -i, the relative differences are comparing the corresponding queries in the baseline rather than the current queries with each other.

If the -m client flag is given, the time is measured using the wallclock time of sqlbench which includes network overhead.

Planning time is excluded by default, but can be included using the -p flag.

The filenames init.sql and destroy.sql are special, and are executed once before and after the benchmark respectively. They can be used to setup or teardown tables, indexes, etc..

Tutorial

Let's say you want to compare three different queries for computing the running total of all numbers from 1 to 1000. Your first idea is to use a window function:

SELECT i, sum(i) OVER (ORDER BY i) AS sum
FROM generate_series(1, 1000) g(i);

Then you decide to get fancy and implement it as a recursive CTE:

WITH RECURSIVE sums AS (
	SELECT 1 AS i, 1 AS sum
	UNION
	SELECT i+1, sum+i FROM sums WHERE i  examples/sum/gauss.sql  examples/sum/window.sql  examples/sum/recursive.sql <==
WITH RECURSIVE sums AS (
	SELECT 1 AS i, 1 AS sum
	UNION
	SELECT i+1, sum+i FROM sums WHERE i <= 1000
)

SELECT * FROM sums;

And finally, you can use the -m client flag to measure the query times without EXPLAIN ANALYZE to see if that had a significant overhead:

$ sqlbench -s -n 1000 -m client examples/sum/*.sql | tee client-bench.txt

         | gauss |    window    |  recursive
---------+-------+--------------+---------------
  n      |  1000 |         1000 |         1000
  min    |  0.66 | 1.44 (2.18x) | 2.03 (3.08x)
  max    |  5.66 | 7.31 (1.29x) | 4.34 (0.77x)
  mean   |  0.83 | 1.72 (2.08x) | 2.35 (2.83x)
  stddev |  0.23 | 0.33 (1.41x) | 0.27 (1.18x)
  median |  0.78 | 1.65 (2.11x) | 2.26 (2.89x)
  p90    |  0.98 | 1.98 (2.03x) | 2.68 (2.75x)
  p95    |  1.05 | 2.13 (2.03x) | 2.89 (2.76x)

Stopping after 1000 iterations as requested.

Indeed, it appears that from the client's perspective the gauss query is a bit slower, while the others are a bit faster when measuring without EXPLAIN ANALYZE. Whether that's a rabbit hole worth exploring depends on you, but either way you now have a much better sense of the errors that might be contained in your measurements.

Todos

Below are a few ideas for todos that I might implement at some point or would welcome as pull requests.

• Dynamically adjust unit between ms, s, etc.
• Support specifying benchmarks using a single YAML file.
• Support for other databases, e.g. MySQL.
• Capture query plans for each query, ideally one close to the median execution time.
• Provide an easy way to capture all inputs and outputs in a single tar.gz file or GitHub gist.
• Plot query times as a histogram (made a proof of concept for this, but didn't like it enough yet to release)
• Maybe add db name to verbose output, see request.
• Compare benchmark results between PG versions
• Oneliner examples for README
• Warmup phase (can be done via init.sql and pg_prewarm()
• Use TIMING OFF to reduce EXPLAIN overhead.
• A flag to include planning time in -m explain mode.
• A flag to use prepared queries in -m client mode.

License

sqlbench is licensed under the MIT license.