index only scan question

Поиск
Список
Период
Сортировка
От Daniel Westermann
Тема index only scan question
Дата
Msg-id AM4PR0901MB1346BBF200903002F6E74539D2C60@AM4PR0901MB1346.eurprd09.prod.outlook.com
обсуждение исходный текст
Ответы Re: index only scan question
Re: index only scan question
Список pgsql-general
Hi quick

question: Given these steps:


postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using i2 on t1 (actual time=0.056..0.058 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 0
   Buffers: shared hit=4
 Planning time: 0.421 ms
 Execution time: 0.111 ms
(6 rows)

postgres=# update t1 set a = 30 where b = 5;
UPDATE 1
postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using i2 on t1 (actual time=0.039..0.042 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 2
   Buffers: shared hit=5
 Planning time: 0.176 ms
 Execution time: 0.082 ms

The 2 heap fetches for the second run are clear to me, because of the pointer from the old version of the row to the
newone. But why does the next execution only need one heap fetch? 


postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Only Scan using i2 on t1 (actual time=0.046..0.049 rows=1 loops=1)
   Index Cond: (b = 5)
   Heap Fetches: 1
   Buffers: shared hit=5
 Planning time: 0.194 ms
 Execution time: 0.097 ms

Is that because of some sort of caching?

Thanks in advance
Daniel









Вложения

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

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: Full list of operations that constitute a "maintenance"operation?
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: index only scan question