How do I compare two SQL queries to run on Postgres

I need to compare two queries that will run in my Postgres database.

How do I know the execution time and any other statistics of them so I can produce a reliable benchmark between them?

Answer

I can think of two interesting data points to collect and compare:

1. The execution time.

   For that, simply execute the query using psql connected via UNIX sockets (to factor out the network) and use psql‘s timing command to measure the execution time as seen on the client.

   Do not use EXPLAIN (ANALYZE) for that since that would add notable overhead which affects your measurements.

   Make sure to run the query several times to get a reliable number. That number will correspond to the execution time with a warm cache.

   If you want to measure execution time with a cold cache, restart PostgreSQL and empty the file system cache.

2. The number of blocks touched by the query.

   For that, run EXPLAIN (ANALYZE, BUFFERS) once for each query.

   The number of blocks touched is significant for performance: the fewer blocks a query touches, the faster it will (often) be. This number is particularly significant for performance with a cold cache; the fewer blocks, the less execution time will depend on caching.