优化 Postgres 删除孤立记录

2023-11-25

取下面两张表：

Table "public.contacts"
       Column       |            Type             |                       Modifiers                       | Storage  | Stats target | Description
--------------------+-----------------------------+-------------------------------------------------------+----------+--------------+-------------
 id                 | integer                     | not null default nextval('contacts_id_seq'::regclass) | plain    |              |
 created_at         | timestamp without time zone | not null                                              | plain    |              |
 updated_at         | timestamp without time zone | not null                                              | plain    |              |
 external_id        | integer                     |                                                       | plain    |              |
 email_address      | character varying           |                                                       | extended |              |
 first_name         | character varying           |                                                       | extended |              |
 last_name          | character varying           |                                                       | extended |              |
 company            | character varying           |                                                       | extended |              |
 industry           | character varying           |                                                       | extended |              |
 country            | character varying           |                                                       | extended |              |
 region             | character varying           |                                                       | extended |              |
 ext_instance_id    | integer                     |                                                       | plain    |              |
 title              | character varying           |                                                       | extended |              |
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (id)
    "index_contacts_on_ext_instance_id_and_external_id" UNIQUE, btree (ext_instance_id, external_id)

and

Table "public.members"
        Column         |            Type             |                             Modifiers                              | Storage  | Stats target | Description
-----------------------+-----------------------------+--------------------------------------------------------------------+----------+--------------+-------------
 id                    | integer                     | not null default nextval('members_id_seq'::regclass)               | plain    |              |
 step_id               | integer                     |                                                                    | plain    |              |
 contact_id            | integer                     |                                                                    | plain    |              |
 rule_id               | integer                     |                                                                    | plain    |              |
 request_id            | integer                     |                                                                    | plain    |              |
 sync_id               | integer                     |                                                                    | plain    |              |
 status                | integer                     | not null default 0                                                 | plain    |              |
 matched_targeted_rule | boolean                     | default false                                                      | plain    |              |
 external_fields       | jsonb                       |                                                                    | extended |              |
 imported_at           | timestamp without time zone |                                                                    | plain    |              |
 campaign_id           | integer                     |                                                                    | plain    |              |
 ext_instance_id       | integer                     |                                                                    | plain    |              |
 created_at            | timestamp without time zone |                                                                    | plain    |              |
Indexes:
    "members_pkey" PRIMARY KEY, btree (id)
    "index_members_on_contact_id_and_step_id" UNIQUE, btree (contact_id, step_id)
    "index_members_on_campaign_id" btree (campaign_id)
    "index_members_on_step_id" btree (step_id)
    "index_members_on_sync_id" btree (sync_id)
    "index_members_on_request_id" btree (request_id)
    "index_members_on_status" btree (status)

主键和索引都存在members.contact_id.

我需要删除任何contact其中没有相关的members。大约有3MMcontact和25MMmember记录。

我正在尝试以下两个查询：

Query 1:

DELETE FROM "contacts"
WHERE  "contacts"."id" IN (SELECT "contacts"."id" 
                           FROM   "contacts" 
                                  LEFT OUTER JOIN members 
                                               ON 
                                  members.contact_id = contacts.id 
                           WHERE  members.id IS NULL);

DELETE 0
Time: 173033.801 ms

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on contacts  (cost=2654306.79..2654307.86 rows=1 width=18) (actual time=188717.354..188717.354 rows=0 loops=1)
   ->  Nested Loop  (cost=2654306.79..2654307.86 rows=1 width=18) (actual time=188717.351..188717.351 rows=0 loops=1)
         ->  HashAggregate  (cost=2654306.36..2654306.37 rows=1 width=16) (actual time=188717.349..188717.349 rows=0 loops=1)
               Group Key: contacts_1.id
               ->  Hash Right Join  (cost=161177.46..2654306.36 rows=1 width=16) (actual time=188717.345..188717.345 rows=0 loops=1)
                     Hash Cond: (members.contact_id = contacts_1.id)
                     Filter: (members.id IS NULL)
                     Rows Removed by Filter: 26725870
                     ->  Seq Scan on members  (cost=0.00..1818698.96 rows=25322396 width=14) (actual time=0.043..160226.686 rows=26725870 loops=1)
                     ->  Hash  (cost=105460.65..105460.65 rows=3205265 width=10) (actual time=1962.612..1962.612 rows=3196180 loops=1)
                           Buckets: 262144  Batches: 4  Memory Usage: 34361kB
                           ->  Seq Scan on contacts contacts_1  (cost=0.00..105460.65 rows=3205265 width=10) (actual time=0.011..950.657 rows=3196180 loops=1)
         ->  Index Scan using contacts_pkey on contacts  (cost=0.43..1.48 rows=1 width=10) (never executed)
               Index Cond: (id = contacts_1.id)
 Planning time: 0.488 ms
 Execution time: 188718.862 ms

Query 2:

DELETE FROM contacts 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   members c 
                   WHERE  c.contact_id = contacts.id); 

DELETE 0
Time: 170871.219 ms

-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on contacts  (cost=2258873.91..2954594.50 rows=1895601 width=12) (actual time=177523.034..177523.034 rows=0 loops=1)
   ->  Hash Anti Join  (cost=2258873.91..2954594.50 rows=1895601 width=12) (actual time=177523.029..177523.029 rows=0 loops=1)
         Hash Cond: (contacts.id = c.contact_id)
         ->  Seq Scan on contacts  (cost=0.00..105460.65 rows=3205265 width=10) (actual time=0.018..1068.357 rows=3196180 loops=1)
         ->  Hash  (cost=1818698.96..1818698.96 rows=25322396 width=10) (actual time=169587.802..169587.802 rows=26725870 loops=1)
               Buckets: 262144  Batches: 32  Memory Usage: 36228kB
               ->  Seq Scan on members c  (cost=0.00..1818698.96 rows=25322396 width=10) (actual time=0.052..160081.880 rows=26725870 loops=1)
 Planning time: 0.901 ms
 Execution time: 177524.526 ms

正如您所看到的，即使不删除任何记录，两个查询也显示出相似的性能，大约需要 3 分钟。

服务器磁盘 I/O 峰值达到 100%，因此我假设数据正在溢出到磁盘，因为在两个磁盘上都进行了顺序扫描contacts and members.

服务器是 EC2 r3.large（15GB RAM）。

关于我可以做什么来优化这个查询有什么想法吗？

更新#1：

运行后vacuum analyze对于两个表并确保enable_mergejoin被设定为on查询时间没有区别：

DELETE FROM contacts 
WHERE  NOT EXISTS (SELECT 1 
                   FROM   members c 
                   WHERE  c.contact_id = contacts.id); 

-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on contacts  (cost=2246088.17..2966677.08 rows=1875003 width=12) (actual time=209406.342..209406.342 rows=0 loops=1)
   ->  Hash Anti Join  (cost=2246088.17..2966677.08 rows=1875003 width=12) (actual time=209406.338..209406.338 rows=0 loops=1)
         Hash Cond: (contacts.id = c.contact_id)
         ->  Seq Scan on contacts  (cost=0.00..105683.28 rows=3227528 width=10) (actual time=0.008..1010.643 rows=3227462 loops=1)
         ->  Hash  (cost=1814029.74..1814029.74 rows=24855474 width=10) (actual time=198054.302..198054.302 rows=27307060 loops=1)
               Buckets: 262144  Batches: 32  Memory Usage: 37006kB
               ->  Seq Scan on members c  (cost=0.00..1814029.74 rows=24855474 width=10) (actual time=1.132..188654.555 rows=27307060 loops=1)
 Planning time: 0.328 ms
 Execution time: 209408.040 ms

更新2：

PG版本：

PostgreSQL 9.4.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo Hardened 4.5.4 p1.0, pie-0.4.7) 4.5.4, 64-bit

关系尺寸：

         Table         |  Size   | External Size
-----------------------+---------+---------------
 members               | 23 GB   | 11 GB
 contacts              | 944 MB  | 371 MB

设置：

 work_mem
----------
 64MB

 random_page_cost
------------------
 4

更新3：

尝试批量执行此操作似乎对 I/O 使用率没有帮助（仍然飙升至 100%），并且尽管使用了基于索引的计划，但似乎也没有按时改进。

DO $do$ 
BEGIN 
  FOR i IN 57..668 
  LOOP 
    DELETE 
    FROM   contacts 
    WHERE  contacts.id IN 
           ( 
                           SELECT          contacts.id 
                           FROM            contacts 
                           left outer join members 
                           ON              members.contact_id = contacts.id 
                           WHERE           members.id IS NULL 
                           AND             contacts.id >= (i    * 10000) 
                           AND             contacts.id < ((i+1) * 10000));
END LOOP;END $do$;

我不得不在之后终止查询Time: 1203492.326 ms在查询运行的整个过程中，磁盘 I/O 保持在 100%。我还尝试了 1,000 和 5,000 个块，但没有看到性能有任何提高。

注：57..668使用范围是因为我知道这些是现有的联系人 ID。（例如。min(id) and max(id))

关于我可以做什么来优化这个查询有什么想法吗？

你的查询很完美。我会用NOT EXISTS变体。
您的索引index_members_on_contact_id_and_step_id也有好处：

复合索引也适合第一个字段的查询吗？

但请参阅下文有关 BRIN 指数的内容。

您可以调整服务器、表和索引配置。

由于您几乎不更新或删除任何行，根据您的评论，重点关注优化read表现。

1.升级你的Postgres版本

您提供：

服务器是 EC2 r3.large（15GB RAM）。

And:

PostgreSQL 9.4.4

你的版本已经严重过时了。At least升级到最新的小版本。更好的是，升级到当前的主要版本。 Postgres 9.5 和 9.6 为大数据带来了重大改进——这正是您所需要的。

考虑项目的版本控制策略。

亚马逊允许您升级！

2. 改进表统计

在基本顺序扫描中，预期行数与实际行数之间存在意外的 10% 不匹配：

对成员 c 进行 Seq 扫描（成本=0.00..1814029.74行=24855474宽度=10）（实际时间=1.132..188654.555行=27307060循环=1)

一点也不引人注目，但仍然不应该出现在这个查询中。表示您可能需要调整您的autovacuum设置 - 对于非常大的桌子来说可能是每张桌子。

问题比较多：

哈希反连接（成本=2246088.17..2966677.08行=1875003宽度=12）（实际时间=209406.338..209406.338rows=0循环=1)

Postgres 期望找到 1875003 行要删除，而实际上找到 0 行。这是出乎意料的。也许大幅提高统计目标members.contact_id and contacts.id可以帮助缩小差距，从而实现更好的查询计划。看：

防止 PostgreSQL 有时选择错误的查询计划

3.避免表和索引膨胀

您的 ~ 25MM 行members占用 23 GB - 每行几乎 1kb，这对于您提供的表定义来说似乎过多（即使您提供的总大小应包括索引）：

 4 bytes  item identifier

24        tuple header
 8        null bitmap
36        9x integer
16        2x ts
 1        1x bool
??        1x jsonb

See:

理解 Postgres 行大小

每行 89 个字节 - 或者更少，有一些 NULL 值 - 并且几乎没有任何对齐填充，所以最大 96 字节，加上你的jsonb column.

Either that jsonb列非常大，这让我建议将数据规范化为单独的列或单独的表。考虑：

如何对 JSONB 类型的列执行更新操作

Or你的桌子很臃肿，这可以解决VACUUM FULL ANALYZE或者，当这样做时：

CLUSTER members USING index_members_on_contact_id_and_step_id;
VACUUM members;

但要么在表上获取独占锁，你说你负担不起。pg_repack无需独占锁即可做到。看：

VACUUM 将磁盘空间返回给操作系统

即使我们考虑索引大小，您的表似乎太大了：您有 7 个小索引，每个索引每行 36 - 44 字节，没有膨胀，NULL 值更少，因此总共

无论哪种方式，请考虑更具攻击性autovacuum设置为您的餐桌members。有关的：

PostgreSQL 上的主动 Autovacuum
缓存表的填充因子是多少？

和/或从一开始就停止让表格膨胀。您是否经常更新行？您经常更新哪个特定专栏？那jsonb专栏也许？您可以将其移动到一个单独的（1:1）表，只是为了停止用死元组使主表膨胀 - 并保持autovacuum从做它的工作。

4. 尝试 BRIN 索引

块范围索引需要 Postgres9.5 或更高版本 and 戏剧性地减小索引大小。我对初稿过于乐观了。 BRIN 指数是perfect对于您的用例，如果您有many行中members对于每个contact.id- 后物理集群至少一次（参见 ③ 的装修CLUSTER命令）。在这种情况下，Postgres 可以快速排除整个数据页。但你的数字表明每个只有大约 8 行contact.id，因此数据页通常会包含多个值，这会消除大部分效果。取决于您的数据分布的实际细节......

另一方面，就目前情况而言，元组大小约为 1 kb，因此每个数据页只有约 8 行（通常为 8kb）。如果这不是主要的膨胀，那么 BRIN 索引毕竟可能会有所帮助。

但您需要先升级您的服务器版本。参见①。

CREATE INDEX members_contact_id_brin_idx ON members USING BRIN (contact_id);

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)