How to Make PostgreSQL Query Slow

Posted by Szymon Guz

Some applications can be very vulnerable to long running queries. When you test an application, sometimes it is good to have a query running for, let's say, 10 minutes. What's more it should be a normal query, so the application can get the normal results, however this query should run for some longer time than usual.

PostgreSQL has quite a nice function pg_sleep which takes exactly one parameter, it is the number of seconds this function will wait before returning. You can use it as a normal PostgreSQL function, however it's not very sensible:

# SELECT pg_sleep(10);

 pg_sleep 
----------
 
(1 row)

Time: 10072.794 ms

The most interesting usage is adding this function into a query. Let's take this query:

# SELECT schemaname, tablename 
  FROM pg_tables 
  WHERE schemaname <> 'pg_catalog';

Time: 0.985 ms

As you can see, this query is quite fast and returns data in less than 1 ms. Let's now make this query much slower, however returning exactly the same data, but after 15 seconds:

# SELECT schemaname, tablename 
  FROM pg_tables, pg_sleep(15) 
  WHERE schemaname <> 'pg_catalog';

Time: 15002.084 ms

In fact the query execution time is a little bit longer, the pg_sleep function was waiting 15 seconds, but PostgreSQL had to spend some time on query parsing, execution and returning proper data.

I was using this solution many times to simulate a long running query, without changing the application logic, to check how the application behaves during some load peaks.