PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15830
PG Version12beta1
OSCentos 7.x x64
Opened2019-06-01 04:00:48+00
Reported byZhou Digoal
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

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

Messages

DateAuthorSubject
2019-06-01 04:00:48+00PG Bug reporting formBUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?
2019-06-01 12:21:39+00Peter GeogheganRe: BUG #15830: index pg_stat_all_indexes.idx_tup_read statistic error?