BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: digoal(at)126(dot)com
Subject: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?
Date: 2019-06-01 04:00:48
Message-ID: 15830-bb9bb19722c42076@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15830
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2019-06-01 12:21:39 Re: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?
Previous Message David G. Johnston 2019-05-31 21:01:15 Re: BUG #15829: PdAdmin 4 gives a Server error trigger/sql/#110003#/properties.sql with VIEWS