pg中删除的页是否仍被访问

昨天看到微信群中,有人提问:pg对于标记为删除的页,是否会扫描到?

今天做了一下测试,发现如果删除的是表的最后连续的几个页(根据ctid来确定数据插入先后,只讨论有insert的情况)中的数据,最后几个页经过vacuum后,会被释放回操作系统,自然不会被扫描到,但如果不是最后的页会不会扫描到,下面测试一下。

1. 测试无索引的情况:

swrd=# \d t1
                       Table "swrd.t1"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 c1     | integer           |           |          |
 c2     | character varying |           |          |
 c3     | character varying |           |          |
 c4     | character varying |           |          |
swrd=# insert into t1 select i,md5(i::text),md5(i::text),md5(i::text) from generate_series(1,10000) t(i);
INSERT 0 10000
swrd=# select max(ctid) from t1;
   max
----------
 (40,80)
(1 row)

在t1中插入1w条记录,t1表是一共占用了41个页,查看记录,一个块中存放248条记录。

##全表扫描,可以看到buffers数是41个。
swrd=# explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  ;
                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Seq Scan on swrd.t1  (cost=0.00..141.00 rows=10000 width=103) (actual time=0.020..1.696 rows=10000 loops=1)
   Output: c1, c2, c3, c4
   Buffers: shared hit=41
 Planning Time: 0.142 ms
 Execution Time: 2.650 ms
(5 rows)

##删除几个页,看是否有变化,不做vacuum
##删除第13页到16页
swrd=# alter table t1 set(autovacuum_enabled=off);
ALTER TABLE
swrd=# delete from t1 where c1>=2977 and c1<=4216;
DELETE 1240
##仍然读取的buffer个数是41个,因为页还没做清理,所以很正常,下面做一下vacuum
swrd=# explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  ;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on swrd.t1  (cost=0.00..128.60 rows=8760 width=103) (actual time=0.066..2.961 rows=8760 loops=1)
   Output: c1, c2, c3, c4
   Buffers: shared read=41
 Planning Time: 0.694 ms
 Execution Time: 3.836 ms
(5 rows)
##做一下vacuum
swrd=# vacuum VERBOSE t1;
INFO:  vacuuming "swrd.t1"
INFO:  "t1": removed 0 row versions in 5 pages
INFO:  "t1": found 0 removable, 8760 nonremovable row versions in 41 out of 41 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 46060313
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  vacuuming "pg_toast.pg_toast_37066"
INFO:  index "pg_toast_37066_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "pg_toast_37066": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 46060313
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
swrd=# explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  ;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on swrd.t1  (cost=0.00..128.60 rows=8760 width=103) (actual time=0.011..1.162 rows=8760 loops=1)
   Output: c1, c2, c3, c4
   Buffers: shared hit=41
 Planning Time: 0.092 ms
 Execution Time: 1.972 ms
(5 rows)

扫描的page仍然是41个。在做vacuum full后,扫描的页数才减少了5个。

swrd=# vacuum FULL t1;
VACUUM
swrd=# explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  ;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Seq Scan on swrd.t1  (cost=0.00..123.60 rows=8760 width=103) (actual time=0.057..2.040 rows=8760 loops=1)
   Output: c1, c2, c3, c4
   Buffers: shared read=36
 Planning Time: 0.089 ms
 Execution Time: 2.879 ms
(5 rows)

2. 测试使用索引的情况:

swrd=# create index on t1(c1);
CREATE INDEX
swrd=#   explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  where c1>=2977 and c1<=4216 ;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_c1_idx on swrd.t1  (cost=0.29..42.08 rows=1240 width=103) (actual time=0.098..0.415 rows=1240 loops=1)
   Output: c1, c2, c3, c4
   Index Cond: ((t1.c1 >= 2977) AND (t1.c1 <= 4216))
   Buffers: shared hit=7
 Planning Time: 0.143 ms
 Execution Time: 0.558 ms
(6 rows)

c1值在2977-4216共占用了5个页,其他两个页是来自索引的。

使用插件pageinspect看一下索引的结构:

swrd=# select * from bt_metap('t1_c1_idx');
 magic  | version | root | level | fastroot | fastlevel | oldest_xact | last_cleanup_num_tuples
--------+---------+------+-------+----------+-----------+-------------+-------------------------
 340322 |       3 |    3 |     1 |        3 |         1 |           0 |                      -1
(1 row)

看到索引的level是1,也就是该索引是2级结构,包含了meta page,root page,leaf page。另外读取的buffer是root page和leaf page。

下面删掉其中的两个页的数据量:

swrd=# delete from t1 where c1 >=3225 and c1<=3720;
DELETE 496
swrd=#  explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  where c1>=2977 and c1<=4216
swrd-# ;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_c1_idx on swrd.t1  (cost=0.29..42.08 rows=1240 width=103) (actual time=0.086..0.428 rows=744 loops=1)
   Output: c1, c2, c3, c4
   Index Cond: ((t1.c1 >= 2977) AND (t1.c1 <= 4216))
   Buffers: shared hit=8 dirtied=1
 Planning Time: 0.141 ms
 Execution Time: 0.528 ms
(6 rows)

swrd=#  explain (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING) select * from t1  where c1>=2977 and c1<=4216
;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_c1_idx on swrd.t1  (cost=0.29..42.08 rows=1240 width=103) (actual time=0.064..0.253 rows=744 loops=1)
   Output: c1, c2, c3, c4
   Index Cond: ((t1.c1 >= 2977) AND (t1.c1 <= 4216))
   Buffers: shared hit=5
 Planning Time: 0.125 ms
 Execution Time: 0.348 ms
(6 rows)

通过使用bt_page_items查看索引中的数据,一个索引页中包含了1473条记录,并可以看到删除的页均在一个leaf page上面。

由于我们之前避免vacuum的干扰,将它关闭了,手动执行一下,可以看到对应的索引记录也被删除了。此处删除的数据是(13,1)到(14,248)的记录。

swrd=# select * from bt_page_items('t1_c1_idx',4);
 itemoffset |   ctid   | itemlen | nulls | vars |          data
------------+----------+---------+-------+------+-------------------------
          1 | (17,201) |      16 | f     | f    | 41 11 00 00 00 00 00 00
          2 | (11,217) |      16 | f     | f    | 81 0b 00 00 00 00 00 00
          3 | (11,218) |      16 | f     | f    | 82 0b 00 00 00 00 00 00
          4 | (11,219) |      16 | f     | f    | 83 0b 00 00 00 00 00 00
          5 | (11,220) |      16 | f     | f    | 84 0b 00 00 00 00 00 00
          6 | (11,221) |      16 | f     | f    | 85 0b 00 00 00 00 00 00
          7 | (11,222) |      16 | f     | f    | 86 0b 00 00 00 00 00 00
          8 | (11,223) |      16 | f     | f    | 87 0b 00 00 00 00 00 00
          9 | (11,224) |      16 | f     | f    | 88 0b 00 00 00 00 00 00
         10 | (11,225) |      16 | f     | f    | 89 0b 00 00 00 00 00 00
         11 | (11,226) |      16 | f     | f    | 8a 0b 00 00 00 00 00 00
         12 | (11,227) |      16 | f     | f    | 8b 0b 00 00 00 00 00 00
         13 | (11,228) |      16 | f     | f    | 8c 0b 00 00 00 00 00 00
         14 | (11,229) |      16 | f     | f    | 8d 0b 00 00 00 00 00 00
         15 | (11,230) |      16 | f     | f    | 8e 0b 00 00 00 00 00 00
         16 | (11,231) |      16 | f     | f    | 8f 0b 00 00 00 00 00 00
         17 | (11,232) |      16 | f     | f    | 90 0b 00 00 00 00 00 00
         18 | (11,233) |      16 | f     | f    | 91 0b 00 00 00 00 00 00
         19 | (11,234) |      16 | f     | f    | 92 0b 00 00 00 00 00 00
         20 | (11,235) |      16 | f     | f    | 93 0b 00 00 00 00 00 00
.....
        277 | (12,244) |      16 | f     | f    | 94 0c 00 00 00 00 00 00
        278 | (12,245) |      16 | f     | f    | 95 0c 00 00 00 00 00 00
        279 | (12,246) |      16 | f     | f    | 96 0c 00 00 00 00 00 00
        280 | (12,247) |      16 | f     | f    | 97 0c 00 00 00 00 00 00
        281 | (12,248) |      16 | f     | f    | 98 0c 00 00 00 00 00 00
        282 | (15,1)   |      16 | f     | f    | 89 0e 00 00 00 00 00 00
        283 | (15,2)   |      16 | f     | f    | 8a 0e 00 00 00 00 00 00
        284 | (15,3)   |      16 | f     | f    | 8b 0e 00 00 00 00 00 00
        285 | (15,4)   |      16 | f     | f    | 8c 0e 00 00 00 00 00 00
....
        769 | (16,240) |      16 | f     | f    | 70 10 00 00 00 00 00 00
        770 | (16,241) |      16 | f     | f    | 71 10 00 00 00 00 00 00
        771 | (16,242) |      16 | f     | f    | 72 10 00 00 00 00 00 00
        772 | (16,243) |      16 | f     | f    | 73 10 00 00 00 00 00 00
        773 | (16,244) |      16 | f     | f    | 74 10 00 00 00 00 00 00
        774 | (16,245) |      16 | f     | f    | 75 10 00 00 00 00 00 00
        775 | (16,246) |      16 | f     | f    | 76 10 00 00 00 00 00 00
        776 | (16,247) |      16 | f     | f    | 77 10 00 00 00 00 00 00
        777 | (16,248) |      16 | f     | f    | 78 10 00 00 00 00 00 00
        778 | (17,1)   |      16 | f     | f    | 79 10 00 00 00 00 00 00
....

看到上面的执行计划,第一次执行时是有8个buffer,其中一个是脏页,这里后面再深入了解一下,再补充上,第二次时,读取的buffer数就变成了5个,我们删除的两个页已经不再了。也就是使用索引扫描时没有扫描到删除的页,这个也很好理解,因为索引是根据ctid来扫描的,删除的页不存在要查询的数据了,自然就不会在扫了。

总结:

  1. 全表扫描时,读取数据时可能会扫描到删除的页面。(按理说在读数据时,只需要读取buftag对应的页就可以了,但测试发现膨胀产生的脏页仍会被读到。很疑惑。)
  2. 索引扫描时,不会扫描到删除的页面,但对于有hot操作的记录,仍需要使用原记录的line pointer。

posted on 2019-03-13 20:21 Still water run deep 阅读(...) 评论(...) 编辑 收藏

导航

公告