the sql was slow with first time run in PostgreSQL 14

Question

I have a SQL statement that runs slow the first time. The second time it will take only 6s and the first time will take more than 50s.

How can I reproduce my observations with the first run of the query? I have tried:

discard all;
explain (analyze, buffers) select......

How to clear the buffer and set the statement to run with the initial state of the system?

I cannot restart the PostgreSQL instance, because it is used for many applications. Maybe the first time did not contains buffers and the second time use buffers. The hard part is that I can hardly capture the first time query execution plan.

• Laurenz Albe: You could blow away the cache by using pg_prewarm() to load a large table.
• Dolphin: I have already tried pg_prewarm but the buffer size seems small that the cached index will swap out from buffer, then the sql still slow without buffer, seems the only way is to increase the postgresql buffer memory and add memory for my server.
• Laurenz Albe: That makes sense, but has little connection to your question.

Answer 1

This could be due to the standard behaviour of most DBMS.

The first time you execute a query the data might not be in the cache (buffer cache, data cache, ....). Once the statement has run the first time, all of the required data is located in RAM (buffercache, data cache, ...).

Unless of course you DBMS has been configured to use a very small amount of memory. In that case even the second run of the same query might run slow.

Solutions

1. Instead of trying to clear the cache completely, which would have a similar effect as restarting the instance (all data is removed from cache, affecting all users), you might want to consider adding more RAM to the server and assigning more RAM to the DBMS.

2. You might be able to evict plans for certain statements using the pg_buffercache view and the pg_buffercache_evict() function.