4

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?

Improve this question

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Maybe restart + pg_prewarm will produce the most "real" times?
0

A metadata change should automatically invalidate the affected plans. So altering the table, creating/dropping indexes should do it and you don't need to do anything special.

enter image description here

The ANALYZE command also does it.

Improve this answer

Comments

-1

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.

Improve this answer

3 Comments

Thanks! What from the above actually clears the so called "cache"? It's a cloned database with only me working on, one query at a time - do I really need all these commands or it's an overkill? Plus, what's a "transaction block"?
Transaction block means BEGIN .. COMMIT. This command is mainly used to remove previous execution plan of current session.
this does not really answer the question, what is the command for cleaning shared buffers ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.