BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?

Поиск
Список
Период
Сортировка
От PG Bug reporting form
Тема BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?
Дата
Msg-id 15830-bb9bb19722c42076@postgresql.org
обсуждение исходный текст
Ответы Re: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?  (Peter Geoghegan <pg@bowt.ie>)
Список pgsql-bugs
The following bug has been logged on the website:

Bug reference:      15830
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 12beta1
Operating system:   Centos 7.x x64
Description:

When i use postgresql idx_tup_read compute how many index leaf page's ctid
scans, i found there is somthing strange phenomenon.

```
postgres=# alter table h set (autovacuum_enabled =off);
ALTER TABLE
postgres=# delete from h where ctid = any (array ( select ctid from h where
id=2 limit 100));
DELETE 100
postgres=# select * from pg_stat_all_indexes where indexrelname='idx_h_1';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan |
idx_tup_read | idx_tup_fetch 
-------+------------+------------+---------+--------------+----------+--------------+---------------
 25052 |      25058 | public     | h       | idx_h_1      |        3 |
  2076 |          1088
(1 row)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from h
where id=2;
                                                       QUERY PLAN
                                            

-------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_h_1 on public.h  (cost=0.42..1801.36 rows=968 width=9)
(actual time=0.765..1.820 rows=888 loops=1)
   Output: id, info
   Index Cond: (h.id = 2)
   Buffers: shared hit=905 dirtied=1
 Planning time: 0.076 ms
 Execution time: 1.879 ms
(6 rows)

postgres=# select * from pg_stat_all_indexes where indexrelname='idx_h_1';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan |
idx_tup_read | idx_tup_fetch 
-------+------------+------------+---------+--------------+----------+--------------+---------------
 25052 |      25058 | public     | h       | idx_h_1      |        4 |
  3064 |          1976
(1 row)

postgres=# select 1976-1088;
 ?column? 
----------
      888
(1 row)

postgres=# select 3064-2076;
 ?column? 
----------
      988
(1 row)
```

but when i query it again, leaf scan ctid change to clean(no dead).

```
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from h
where id=2;
                                                       QUERY PLAN
                                            

-------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_h_1 on public.h  (cost=0.42..1801.36 rows=968 width=9)
(actual time=0.024..0.926 rows=888 loops=1)
   Output: id, info
   Index Cond: (h.id = 2)
   Buffers: shared hit=812
 Planning time: 0.076 ms
 Execution time: 0.988 ms
(6 rows)

postgres=# select * from pg_stat_all_indexes where indexrelname='idx_h_1';
 relid | indexrelid | schemaname | relname | indexrelname | idx_scan |
idx_tup_read | idx_tup_fetch 
-------+------------+------------+---------+--------------+----------+--------------+---------------
 25052 |      25058 | public     | h       | idx_h_1      |        5 |
  3952 |          2864
(1 row)

postgres=# select 3952-3064;
 ?column? 
----------
      888
(1 row)

postgres=# select 2864-1976;
 ?column? 
----------
      888
(1 row)
```

The question is, does INDEX SCAN clean up the garbage version of leaf page?
Or it's a bug? 

best regards,
digoal


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

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #15829: PdAdmin 4 gives a Server error trigger/sql/#110003#/properties.sqlwith VIEWS
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?