Our Blog

Ongoing observations by End Point people

How to make a PostgreSQL query slow

By Szymon Lipiński · November 5, 2012

Tags: postgres

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

Archive