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.
