BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?

Started by PG Bug reporting formalmost 7 years ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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

In reply to: PG Bug reporting form (#1)
Re: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?

On Sat, Jun 1, 2019 at 12:01 AM PG Bug reporting form
<noreply@postgresql.org> wrote:

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

Yes, sometimes that happens -- the kill_prior_tuple optimization can
kick in, even with a simple SELECT.

It would be clearer what was going on if you forced a bitmap index
scan (e.g. "set enable_indexscan=off;"). That way, the EXPLAIN
(ANALYZE, BUFFERS) instrumentation will show heap blocks and index
blocks separately.

--
Peter Geoghegan