Benchmarking PostgreSQL queries

In order to benchmark my SQL queries on PostgreSQL databases, I like to check the run time of the queries in two situations: with caching and without caching. If the query runs often and accesses the same pages in memory, these pages will be cached and therefore the run time of the query will be substantially lower. However if the query runs infrequently, accesses different pages in the memory each time, or requires a large set of data that doesn’t fit in the memory, then the query will run much more slowly.

It is usually difficult to know ahead of time exactly how often a query will run and how often the pages it needs will be cached, so when I try to benchmark queries for optimization purposes I always check both cases. I used to do this manually – run the query a few times to get the pages in the cache, and then run it a few more times to measure the run time. Then clear the cache and perform the query a few times, clearing the cache in between each test. This is quite tedious work so I wrote a script to do it for me.

CheckQueryRunTime

Download this script, change the extension to .pl and run it with ‘-help’ to see a detailed usage explanation message. Some usage examples:

[yuval@dev ~]# ./CheckQueryRunTime.pl mydb myuser mypass “select * from mytable where mytimefield > now() – interval ’10 minutes'”

Preparing to run query with cache

Running query with cache

…………………………………………..

With cache: 2.72678 ms. (stddev 0.0763655131587091 ms.)

Running query without cache

……….

Without cache: 418.4086 ms. (stddev 14.083357931969 ms.)

[yuval@dev ~]# ./CheckQueryRunTime.pl mytable myuser mypass “select * from mytable where mytimefield between now() – interval ’10 minutes’ and now()”

Preparing to run query with cache

Running query with cache

…………………………………………..

With cache: 2.04068 ms. (stddev 0.0308184619992597 ms.)

Running query without cache

……….

Without cache: 366.6442 ms. (stddev 13.6194627485802 ms.)

Note: in order to clear the cache the script shuts down the postgresql service, cleans the OS caches, and the restarts the service.

Enjoy!