Clearing cache in PostgreSQL

user_11720066 0
Viewed 1793

My question: How to delete the cache of the database, so that the same query will always take the "real" time to run.

The context: I'm trying to improve runtime for a query. The plan is to run the query once, than run explain on it and add some relevant indexes based on the explaination's output, and finally run the query again.

I was told that caching that occurs in the database might affect the results of my tests.

What is the simplest way to clear the cache, or to have a clean slate for tests in general?

explain caching sql-execution-plan postgresql performance
2 Answers

Restarting the database will clear the database's shared_buffers cache. It will not clear the filesystem cache, which PostgreSQL relies upon heavily.

On Linux, writing 1 into the file /proc/sys/vm/drop_caches will drop the FS cache. (Do this after restarting the database) But you need to be a privileged user to do that. Other OS will have other methods.

It is dubious that this produces times that are more "real". They could easily be less "real". How often do you reboot your production server in reality? Usually better would be to write a driver script that runs the same query repeatedly but with different parameters so that it hits different parts of the data.

user_17212390

DISCARD releases internal resources associated with a database session. This command is useful for partially or fully resetting the session's state. There are several subcommands to release different types of resources; the DISCARD ALL variant subsumes all the others, and also resets additional state. Please try this

SET SESSION AUTHORIZATION DEFAULT;
RESET ALL;
DEALLOCATE ALL;
CLOSE ALL;
UNLISTEN *;
SELECT pg_advisory_unlock_all();
DISCARD PLANS;
DISCARD SEQUENCES;
DISCARD TEMP;

N.B.: DISCARD ALL cannot be executed inside a transaction block.

user_163646250
Related