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?