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

Поиск
Список
Период
Сортировка
От 高健
Тема How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?
Дата
Msg-id CAL454F0HyWX5ksLT4tuRbiANjRDTziacBrvxn2pqanGc=VG2Kw@mail.gmail.com
обсуждение исходный текст
Ответы Re: How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?  ("Albe Laurenz" <laurenz.albe@wien.gv.at>)
Re: How to evaluate "explain analyze" correctly soon after "explain" for the same statement ?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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:

 

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.

 

В списке pgsql-general по дате отправления:

Предыдущее
От: Frank Broniewski
Дата:
Сообщение: Re: Memory issue on FreeBSD
Следующее
От: Achilleas Mantzios
Дата:
Сообщение: Re: Memory issue on FreeBSD