ruby-on-rails - 确定 postgres 中是否存在大量行的有效方法

Question

我有一个幂等后台处理任务，它获取一行信息，进行一些清理并插入到数据库中。我的问题是相同的信息可能会被处理多次。

为了解决这个问题，我根据每行数据的信息创建了一个键(散列)，并在索引上创建了一个唯一约束以防止重复。

问题:我通过执行以下操作检查数据是否已存在于数据库中:

SELECT key FROM items WHERE key IN (key,key,key,key).

我发现这个查询要快一点，但响应还是有些慢

SELECT key FROM items WHERE (key = ANY(VALUES(key),(key)))

然后我对返回的键和我期望的键进行交集，只处理尚不存在的数据。

在表达到 1 亿多之前，这种方法运行良好，我可以一次检查 100 多个键，这会导致大量 IO 扫描和检索每一行。

我的问题:是否有更有效的方法来使用唯一约束和索引来检查是否存在？也许实际上并没有到达每一行的东西？

或者，是否有其他可行的方法？简单地尝试插入并捕获唯一约束违规实际上会更快吗？

简化表定义:

Column         |            Type             |                           Modifiers                           | Storage  | Description
------------------------+-----------------------------+---------------------------------------------------------------+----------+-------------
 id                     | integer                     | not null default nextval('items_id_seq'::regclass) | plain    |
 created_at             | timestamp without time zone | not null                                                      | plain    |
 updated_at             | timestamp without time zone | not null                                                      | plain    |
 key                    | character varying(255)      |                                                               | extended |
 item_attributes        | hstore                      |                                                               | extended |
 item_name              | character varying(255)      |                                                               | plain    |
Indexes:
    "items_pkey" PRIMARY KEY, btree (id)
    "index_items_on_key" UNIQUE, btree (key)

和一个查询计划:

Nested Loop  (cost=0.10..108.25 rows=25 width=41) (actual time=0.315..2.169 rows=25 loops=1)
   ->  HashAggregate  (cost=0.10..0.17 rows=25 width=32) (actual time=0.071..0.097 rows=25 loops=1)
         ->  Values Scan on "*VALUES*"  (cost=0.00..0.09 rows=25 width=32) (actual time=0.009..0.033 rows=25 loops=1)
   ->  Index Scan using index_items_on_key on items  (cost=0.00..4.32 rows=1 width=41) (actual time=0.076..0.077 rows=1 loops=25)
         Index Cond: ((key)::text = "*VALUES*".column1)
 Total runtime: 2.406 ms

Answer 1

您没有说明数据来自何处以及如何处理。这是通用的方法

with to_be_inserted (id, key) as (
    values (1, 'the_hash'), (2, 'another_hash')
)
insert into items (id, key)
select f(id, key)
from to_be_inserted tbi
where not exists (
    select 1
    from items
    where key = tbi.key
);

如果将散列存储为 bytea 而不是 text ，则有可能获得显着的性能提升，因为它是大小的一半，因此索引也减半.并使用较小的 md5 散列。

如果处理不能在 SQL 中完成，这个键查找可能会更快

with might_be_inserted (key) as (
    values ('hash1'), ('hash2')
)
select key 
from might_be_inserted mbi
where not exists (
    select 1
    from items
    where key = mbi.key
)