问题：

优化Postgres删除孤立记录

鞠乐

2023-03-14

以以下两个表格为例：

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)

和

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)

主键和成员都有索引。联系_id。

我需要删除任何没有相关成员的联系人。大约有3MM联系人和25MM成员记录。

我正在尝试以下两个问题：

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

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%，因此我假设数据正在溢出到磁盘，因为对联系人和成员都进行了顺序扫描。

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

我能做些什么来优化这个查询呢？

在为两个表运行真空分析并确保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

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

分批进行试验似乎无助于提高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.326ms和磁盘I/O在查询运行的整个时间内保持在100%。我还尝试了1,000和5,000块，但没有看到任何性能提高。

注：57。。使用了668范围，因为我知道这些是现有的联系人ID。（例如min（id）和max（id））

共有3个答案

东郭宏朗

2023-03-14

更新计划者使用的统计信息，并将启用_mergejoin设置为on：

vacuum analyse members;
vacuum analyse contacts;
set enable_mergejoin to on;

您应该得到一个类似于此的查询计划：

explain analyse
delete from contacts 
where not exists (
    select 1 
    from members c 
    where c.contact_id = contacts.id); 

                               QUERY PLAN
----------------------------------------------------------------------
 Delete on contacts
   ->  Merge Anti Join
         Merge Cond: (contacts.id = c.contact_id)
         ->  Index Scan using contacts_pkey on contacts
         ->  Index Scan using members_contact_id_idx on members c

傅和璧

2023-03-14

我能做些什么来优化这个查询呢？

您的查询非常完美。我会使用Not EXISTS变体。

你的索引index_成员_on_contact_id_和_step_id也很有用：

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

但请参见下文关于BRIN指数的内容。

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

由于您实际上没有更新或删除许多行（根据您的评论，几乎没有任何行），您需要优化读取性能。

您提供了：

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

以及：

PostgreSQL 9.4.4

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

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

亚马逊允许你升级！

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

成员c上的序列扫描（成本=0.00..1814029.74行=24855474宽度=10）（实际时间=1.132..188654.555行=27307060循环=1）

一点也不戏剧性，但在这个查询中仍然不应该出现。表示您可能需要调整自动真空设置，对于非常大的自动真空设置，可能需要按表调整。

更麻烦的是：

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

Postgres希望找到1875003行要删除，而实际上找到0行。这是出乎意料的。可能会大幅增加成员的统计目标。联系人id和联系人。id有助于缩小差距，从而实现更好的查询计划。见：

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

您在成员中的~25MM行占用了23GB的空间，这几乎是每行1kb，对于您提供的表定义来说，这似乎是多余的（即使您提供的总大小应该包括索引）：

 4 bytes  item identifier

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

见：

理解Postgres的行大小

这是每行89个字节——或者更少，有一些空值——几乎没有任何对齐填充，所以最多96个字节，加上您的jsonb列。

无论是jsonb列都非常大，这会让我建议将数据规范化为单独的列或单独的表。考虑：

如何在Postgres 9.4中对JSONB类型的列执行更新操作

或者您的表臃肿，可以使用VACUUM FULL ANALYZE解决，或者在使用时：

CLUSTER members USING index_members_on_contact_id_and_step_id;
VACUUM members;

但这两种选择中的任何一种都会在桌子上独占一把锁，你说你负担不起pg_repack可以在没有独占锁的情况下完成。见：

真空将磁盘空间返回到操作系统

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

无论哪种方式，都可以考虑为您的表成员设置更积极的自动真空设置。相关的：

PostgreSQL上的主动自动真空

和/或从一开始就不要让桌子膨胀。你经常更新行吗？有哪个专栏你经常更新吗？也许是jsonb专栏？您可能会将其移动到一个单独的（1:1）表中，以停止使用死元组膨胀主表，并阻止autovacuum完成其工作。

块范围索引需要Postgres 9.5或更高版本，并且大大减少了索引大小。我在初稿中过于乐观。如果您在成员中为每个contact.id有很多行，那么BRIN索引非常适合您的用例-在物理聚类您的表至少一次之后（参见③以获取合适的CLUSTER命令）。在这种情况下，Postgres可以快速排除整个数据页。但是您的数字表示每个contact.id只有大约8行，因此数据页通常包含多个值，这会使大部分效果无效。取决于您的数据分布的实际细节...

另一方面，目前的元组大小约为1 kb，因此每个数据页只有约8行（通常为8kb）。如果这基本上不是膨胀，那么布林指数终究会有所帮助。

但是你需要先升级你的服务器版本。见①。

CREATE INDEX members_contact_id_brin_idx ON members USING BRIN (contact_id);

郝池暝

2023-03-14

解决这类问题的一种方法是将其分成小块。

DELETE FROM "contacts"
WHERE  "contacts"."id" IN (
    SELECT id
    FROM contacts
    LEFT OUTER JOIN members ON members.contact_id = contacts.id 
    WHERE members.id IS NULL
        AND id >= 1 AND id < 1000
);
DELETE FROM "contacts"
WHERE  "contacts"."id" IN (
    SELECT id
    FROM contacts
    LEFT OUTER JOIN members ON members.contact_id = contacts.id 
    WHERE members.id IS NULL
        AND id >= 1001 AND id < 2000
);

冲洗，重复。尝试不同的块大小，为数据集找到一个最佳块大小，该数据集使用最少的查询，同时将所有查询都保留在内存中。

自然，您会希望编写脚本，可能使用plpgsql或您喜欢的任何脚本语言。

类似资料：

如何删除MySQL中的所有孤立记录？

问题内容：我有3个表MySQL（MyIsam）：如何删除所有没有消息也没有archivedMessage的用户？问题答案：您可以使用：
nhibernate 全部删除孤立项

本文向大家介绍nhibernate 全部删除孤立项，包括了nhibernate 全部删除孤立项的使用技巧和注意事项，需要的朋友参考一下示例保存/更新/删除对象时，请检查关联并保存/更新/删除找到的所有对象。除此之外，当一个对象从关联中删除并且不与另一个对象（孤立的）关联时，也将其删除。
Java JPA CascadeType.ALL不删除孤立项

问题内容：我在使用带有以下映射的JPA删除孤立节点时遇到问题我遇到了孤立角色挂在数据库周围的问题。我可以使用特定于注释的Hibernate标记，但是显然我不想将解决方案绑定到Hibernate实现中。编辑：似乎JPA 2.0将包括对此的支持。问题答案：如果将它与Hibernate一起使用，则必须显式定义批注，该批注可与JPA结合使用。如果你不打算使用，则必须先明确删除子元素，然后再删
从表格中删除孤立物品

问题内容：我正在尝试清理有很多孤立物品的桌子。我正在通过查找空值来检查是否与另一个表有关系，从而达到这一目的。我收到一个错误，左外部联接无效。我正在寻找其他方法的建议，可以从这种破裂的关系中删除这些孤儿问题答案：试试这个：
Hibernate注释映射，JPA 孤立删除

我试图在类别和发布类(OneToMany)之间建立关系，我需要删除所有属于特定类别的发布。我不知道我是否遗漏了一些注释，但这是我在执行查询时的错误: org.springframework.web.util.NestedServletException：请求处理失败；嵌套异常org.hibernate.exception.ConstraintViolationException：无法执行更新查询o
JPA CascadeType.All未删除儿童孤儿记录

有人知道我为什么会有这种行为吗？是我的代码出了bug还是什么问题？

优化Postgres删除孤立记录

共有3个答案

相关问答

相关文章

相关阅读

相关工具

相关文档