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
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 |