Re: How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?

高健 wrote:
> I have one question about the cache clearing.
>
> If I use the following soon after database startup(or first time I use it):
>
> postgres=# explain analyze select id,deptno from gaotab where id=200;

> The result is: the above explain analyze got a total runtime of 47 ms.
>
> But If I restart the database again, and then execute the following:
>
> postgres=# explain select id,deptno from gaotab where id=200;

> postgres=# explain analyze select id,deptno from gaotab where id=200;

> This time I got the total runtime of 0.074ms, obviously the explain analyze benefit from the explain
> statement.

The EXPLAIN will not have a noticable effect on the performance
of the EXPLAIN ANALYZE.

If you actually restarted the PostgreSQL server like you said,
then the difference must be that the file is cached in the file
system cache.

You can verify that be omitting the EXPLAIN in the second run.

> It might not be a big problem in a small system.
>
> But when in a production environment, When I want to use explain and then , soon use explain
> analyze for the same statement,
>
> How can I avoid the influence of cache and get the right answer for evaluating purpose?
>
>
>
> It is not a good idea to restart the database again and again I think.
>
> I wonder is there any method of clearing cache or even clear only a particular part of the cache?

The only way of clearing the cache in database shared memory is to
restart the server.

That's just a simple "pg_ctl restart -m fast -D <datadir>".
I think that's simple enough for tests.

You should also empty the file system cache.
On recent Linux systems that would be
sync; echo 3 > /proc/sys/vm/drop_caches
You'd have to consult the documentation for other OSs.

Yours,
Laurenz Albe