Measuring CPU instructions when querying PostgreSQL

Summary: Michael Stonebraker once published a classic paper titled OLTP Through the Looking Glass, and What We Found There. In it, CPU instruction count was used as a metric to analyze the workload overhead of various database stages.

Table of Contents

Measuring query CPU instructions on PostgreSQL

1. First, let’s create a table in the PostgreSQL database, insert some data, and then vacuum and analyze the table data.

CREATE TABLE t_large (id integer, name text);

INSERT INTO t_large (id, name)
  SELECT i, repeat('Pg', 16)
    FROM generate_series(1, 1000000) AS s(i);

CREATE INDEX large_idx ON t_large (id);
VACUUM ANALYZE t_large;

2. After preparing the base table and initial data, let’s query the backend process ID (PID) of the current session:

SELECT pg_backend_pid();
 pg_backend_pid
----------------
          22328
(1 row)

3. In a Linux command-line terminal, use perf to open the CPU instruction counter and count the instructions executed by process 22328:

$ perf stat -einstructions:u -p 22328

Note: In the command above, the :u in instructions:u specifies counting only instructions executed in user mode. By excluding kernel-mode instructions, we remove interference and achieve more precise results.

4. Return to the previous database session and execute the target SQL:

SELECT * FROM t_large WHERE id = 202601;

5. Return to the perf command window and press Ctrl+C to view the results:

$ perf stat -einstructions:u -p 22328
^C
 Performance counter stats for process id '22328':

           440,708      instructions:u

       6.691347285 seconds time elapsed

Repeat steps 3 through 5 above to observe the test results again:

$ perf stat -einstructions:u -p 22328
^C
 Performance counter stats for process id '22328':

           253,770      instructions:u

       5.804768145 seconds time elapsed

Measuring query CPU instructions on Redrock Postgres

1. First, let’s create a table in the Redrock Postgres database, insert some data, and then vacuum and analyze the table data.

CREATE TABLE t_large (id integer, name text);

INSERT INTO t_large (id, name)
  SELECT i, repeat('Pg', 16)
    FROM generate_series(1, 1000000) AS s(i);

CREATE INDEX large_idx ON t_large (id);
VACUUM ANALYZE t_large;

2. After preparing the base table and initial data, let’s query the backend process ID (PID) of the current session:

SELECT pg_backend_pid();
 pg_backend_pid
----------------
          23030
(1 row)

3. In a Linux command-line terminal, use perf to open the CPU instruction counter and count the instructions executed by process 23030:

$ perf stat -einstructions:u -p 23030

4. Return to the previous database session and execute the target SQL:

SELECT * FROM t_large WHERE id = 202601;

5. Return to the perf command window and press Ctrl+C to view the results:

$ perf stat -einstructions:u -p 23030
^C
 Performance counter stats for process id '23030':

           468,431      instructions:u

       6.048133731 seconds time elapsed

Repeat steps 3 through 5 above to observe the test results again:

$ perf stat -einstructions:u -p 23030
^C
 Performance counter stats for process id '23030':

           237,542      instructions:u

       6.472907115 seconds time elapsed

Test Results Comparison

A summary comparison of the test results above is as follows:

Additionally, we recently improved query performance when data checksums are enabled. The following are the test results based on the development version. (with data checksums enabled):

This improvement will be reflected in the next version of Redrock Postgres.

Note: PostgreSQL 18 changed the default initdb setting to enable data checksums.