AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • 主页
  • 系统&网络
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • 主页
  • 系统&网络
    • 最新
    • 热门
    • 标签
  • Ubuntu
    • 最新
    • 热门
    • 标签
  • Unix
    • 最新
    • 标签
  • DBA
    • 最新
    • 标签
  • Computer
    • 最新
    • 标签
  • Coding
    • 最新
    • 标签
主页 / dba / 问题 / 267970
Accepted
Tuure Laurinolli
Tuure Laurinolli
Asked: 2020-05-28 04:31:12 +0800 CST2020-05-28 04:31:12 +0800 CST 2020-05-28 04:31:12 +0800 CST

极光真空不删除冻结的空白页面?

  • 772

我最近故意删除了我们一张表中的大部分数据。通常,该表主要是追加的,有一个短窗口,在此期间可能会在原始插入之后发生更新。但是,最近大部分数据都被孤立了,我们决定将其删除。

our_db=> delete from my_schema.table where a_column not in (select distinct value from my_schema.another_table);
DELETE 6418263

我希望 VACUUM 能够处理现在大部分为空的页面,但它没有这样做!

our_db=> vacuum(analyze, verbose, disable_page_skipping) my_schema.table;
INFO:  vacuuming "my_schema.table"
INFO:  index "table_pkey" now contains 2 row versions in 46486 pages
DETAIL:  0 index row versions were removed.
46481 index pages have been deleted, 46481 are currently reusable.
CPU: user: 0.21 s, system: 0.00 s, elapsed: 2.76 s.
INFO:  index "table_idx1" now contains 2 row versions in 98919 pages
DETAIL:  0 index row versions were removed.
98914 index pages have been deleted, 98914 are currently reusable.
CPU: user: 0.47 s, system: 0.00 s, elapsed: 5.50 s.
INFO:  index "table_idx2" now contains 2 row versions in 22324 pages
DETAIL:  0 index row versions were removed.
22317 index pages have been deleted, 22317 are currently reusable.
CPU: user: 0.10 s, system: 0.00 s, elapsed: 1.63 s.
INFO:  index "table_idx3" now contains 2 row versions in 22252 pages
DETAIL:  0 index row versions were removed.
22245 index pages have been deleted, 22245 are currently reusable.
CPU: user: 0.11 s, system: 0.00 s, elapsed: 1.68 s.
INFO:  index "table_idx4" now contains 2 row versions in 22373 pages
DETAIL:  0 index row versions were removed.
22366 index pages have been deleted, 22366 are currently reusable.
CPU: user: 0.09 s, system: 0.01 s, elapsed: 1.39 s.
INFO:  index "table_idx5" now contains 2 row versions in 69761 pages
DETAIL:  0 index row versions were removed.
69755 index pages have been deleted, 69755 are currently reusable.
CPU: user: 0.32 s, system: 0.00 s, elapsed: 4.98 s.
INFO:  index "table_idx6" now contains 2 row versions in 71399 pages
DETAIL:  0 index row versions were removed.
71388 index pages have been deleted, 71388 are currently reusable.
CPU: user: 0.31 s, system: 0.02 s, elapsed: 5.55 s.
INFO:  "table": found 0 removable, 2 nonremovable row versions in 754123 out of 754123 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6840941
There were 6439255 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 4.55 s, system: 0.20 s, elapsed: 53.89 s.
INFO:  vacuuming "pg_toast.pg_toast_16458"
INFO:  index "pg_toast_16458_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_16458": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 6840969
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:  analyzing "my_schema.table"
INFO:  "table": scanned 30000 of 754123 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
VACUUM

只有两行是活动的,但仍有超过 750000 页被占用,如果需要顺序扫描,则进行扫描:

our_db=> SELECT count(id) FROM my_schema.table;
 count 
-------
     2
(1 row)

our_db=> explain analyze SELECT count(content) FROM my_schema.table;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=754123.00..754123.01 rows=1 width=8) (actual time=26795.676..26795.676 rows=1 loops=1)
   ->  Seq Scan on table  (cost=0.00..754123.00 rows=1 width=1036) (actual time=25683.699..26795.666 rows=2 loops=1)
 Planning time: 0.052 ms
 Execution time: 26795.703 ms
(4 rows)

启用 disable_page_skipping 后,为什么 VACUUM 不摆脱空的冻结页面?

postgresql aurora
  • 1 1 个回答
  • 301 Views

1 个回答

  • Voted
  1. Best Answer
    Laurenz Albe
    2020-05-28T05:04:47+08:002020-05-28T05:04:47+08:00

    这与冻结无关。

    VACUUM不会缩小表格,只是它会截断表格末尾的任何空页。显然 autovacuum 作业比你的 manual 快VACUUM,因为没有死元组了。

    要缩小和重组表,请运行

    VACUUM (FULL) my_schema.table;
    

    但请注意,这将在操作期间以独占方式锁定表。

    • 0

相关问题

  • 我可以在使用数据库后激活 PITR 吗?

  • 运行时间偏移延迟复制的最佳实践

  • 存储过程可以防止 SQL 注入吗?

  • PostgreSQL 中 UniProt 的生物序列

  • PostgreSQL 9.0 Replication 和 Slony-I 有什么区别?

Sidebar

Stats

  • 问题 205573
  • 回答 270741
  • 最佳答案 135370
  • 用户 68524
  • 热门
  • 回答
  • Marko Smith

    连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目

    • 12 个回答
  • Marko Smith

    如何让sqlplus的输出出现在一行中?

    • 3 个回答
  • Marko Smith

    选择具有最大日期或最晚日期的日期

    • 3 个回答
  • Marko Smith

    如何列出 PostgreSQL 中的所有模式?

    • 4 个回答
  • Marko Smith

    列出指定表的所有列

    • 5 个回答
  • Marko Smith

    如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

    • 4 个回答
  • Marko Smith

    你如何mysqldump特定的表?

    • 4 个回答
  • Marko Smith

    使用 psql 列出数据库权限

    • 10 个回答
  • Marko Smith

    如何从 PostgreSQL 中的选择查询中将值插入表中?

    • 4 个回答
  • Marko Smith

    如何使用 psql 列出所有数据库和表?

    • 7 个回答
  • Martin Hope
    Jin 连接到 PostgreSQL 服务器:致命:主机没有 pg_hba.conf 条目 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane 如何列出 PostgreSQL 中的所有模式? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh 为什么事务日志不断增长或空间不足? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland 列出指定表的所有列 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney MySQL 能否合理地对数十亿行执行查询? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx 如何监控大型 .sql 文件的导入进度? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison 你如何mysqldump特定的表? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 对 SQL 查询进行计时? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas 如何从 PostgreSQL 中的选择查询中将值插入表中? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas 如何使用 psql 列出所有数据库和表? 2011-02-18 00:45:49 +0800 CST

热门标签

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • 主页
  • 问题
    • 最新
    • 热门
  • 标签
  • 帮助

Footer

AskOverflow.Dev

关于我们

  • 关于我们
  • 联系我们

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve