How to make a PostgreSQL query slow
By Szymon Lipiński
November 5, 2012
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.
Comments