Benchmark: Does query formatting affect performance?

postgres performance

Are you leaving some performance on the line by sending indented/formatted SQL to Postgres?

Short answer: no

Long answer:

I used pgbench -i to set up the test db and then benchmarked with a script running

WITH branches AS (
  SELECT * FROM pgbench_branches
)
SELECT bid, COUNT(1) FROM pgbench_tellers
WHERE bid IN (SELECT bid FROM branches)
GROUP BY bid

The results were:

pgbench (17.1 (Homebrew petere/postgresql))
starting vacuum...end.
transaction type: ./simple.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
number of failed transactions: 0 (0.000%)
latency average = 0.057 ms
initial connection time = 12.177 ms
tps = 175582.142594 (without initial connection time)

Then I ran the benchmark with the same query with formatting and copious amounts of whitespace inserted (you can find the whole query in the repo linked below)

Results:

pgbench (17.1 (Homebrew petere/postgresql))
starting vacuum...end.
transaction type: ./whitespace.sql
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
maximum number of tries: 1
number of transactions per client: 10000
number of transactions actually processed: 100000/100000
number of failed transactions: 0 (0.000%)
latency average = 0.057 ms
initial connection time = 8.678 ms
tps = 174543.220392 (without initial connection time)

Notably the average latency is exactly the same for both of the examples.

Keep in mind that the only difference between these 2 scripts is whitespace, other changes that might looks like “just formatting” to you can lead to a change in query performance.

You can find the full benchmark scripts here: https://github.com/pert5432/pg_benchmarks/tree/main/whitespace