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

=?UTF-8?B?6auY5YGl?= <luckyjackgao@gmail.com> writes:

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?

I think this question is based on a false premise. Why do you feel that
the behavior with cold caches is "the right answer", and not the behavior
with warm caches? A short-duration query like this one is not going to
be interesting at all for performance unless it's executed quite a lot,
and if it's executed quite a lot then the warm-cache result ought to be
the more representative one.

In general, trying to tune for cold-cache cases seems backwards to me.
It's much more productive to try to ensure that the caches are warm.

regards, tom lane

Hi tom

At frist I have thought that the database parsed my explain statement,
so the pre-compiled execution plan will be re-used , which made the
statement's second run quick.

I think that what you said is right.

Thank you

2012/11/7 Tom Lane <tgl@sss.pgh.pa.us>