How to evaluate "explain analyze" correctly soon after "explain" for the same statement ? - Mailing list pgsql-general

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:

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.