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

[高健 <luckyjackgao@xxxxxxxxx>]

Hi all:

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;

                                                        QUERY PLAN             

                                          

--------------------------------------------------------------------------------

-------------------------------------------

 Index Only Scan using idx_id_dept on gaotab  (cost=0.00..8.27 rows=1 width=8) (

actual time=30.912..30.915 rows=1 loops=1)

   Index Cond: (id = 200)

   Heap Fetches: 1

 Total runtime: 47.390 ms

(4 rows)

 

postgres=#

 

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;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using idx_id_dept on gaotab  (cost=0.00..8.27 rows=1 width=8)
Index Cond: (id = 200)
(2 rows)
postgres=# explain analyze select id,deptno from gaotab where id=200;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------
Index Only Scan using idx_id_dept on gaotab  (cost=0.00..8.27 rows=1 width=8) (
actual time=0.052..0.053 rows=1 loops=1)
Index Cond: (id = 200)
Heap Fetches: 1
Total runtime: 0.074 ms
(4 rows)

 

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

 

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?

 

In my test environment, I can get the following:

 

postgres=# show seq_page_cost;           

 seq_page_cost            

---------------           

1           

(1 row)           

           

postgres=# show cpu_tuple_cost;           

 cpu_tuple_cost            

----------------           

0.01           

(1 row)           

 

postgres=# show cpu_operator_cost;           

 cpu_operator_cost            

-------------------           

0.0025           

(1 row) 

 

And my table is like that：

postgres=# analyze;    
ANALYZE   
postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;   
 relpages | reltuples | relfilenode | reltype | typname    
----------+-----------+-------------+---------+---------   
        7 |      1000 |       16396 |   16386 | gaotab    
(1 row)

Thanks in advance.