PostgreSQL中最快的检查行是否存在的方法

Question

我有一堆需要插入到表中的行，但这些插入操作总是以批处理的方式完成的。因此，我想要检查批处理中是否存在单个行，如果存在，则说明它们都已插入。

所以这不是主键检查，但应该没关系。我只想检查单个行，因此 count(*) 可能不太好用，所以我猜应该使用 exists。

但由于我对 PostgreSQL 还比较新，所以我宁愿向懂的人请教。

我的批次包含以下结构的行：

userid | rightid | remaining_count

如果表中包含任何带有提供的userid的行，则表示它们全部都存在于那里。

- Valentin Kuzub

---

你想查看表中是否有任何行，或者是否有来自你的批次的任何行？ - JNK

我的批次中有任何一行都是可以的。它们共享同一个字段，我稍微编辑一下。 - Valentin Kuzub

好的，我试图简化实际情况，但我们越来越接近真正的实现。一旦插入这些行（还有另一个字段for_date），我就开始为指定用户递减权限，因为他们使用特定的权限，一旦权限变为0，他们就不能再在那个日期执行这些操作了。这就是真实的故事。 - Valentin Kuzub

只需展示（相关部分的）表定义，并说明您打算做什么。 - wildplasser

[userid, rightid, for_date, remainingCount] 这是真实的表格。当用户尝试使用任何权限时，我打算插入这个表格。我本可以创建一个专门负责每天插入行的进程，但目前我正在检查另一种方法是否在性能方面适合我。 - Valentin Kuzub

Answer 1

使用EXISTS关键词来返回TRUE / FALSE：

select exists(select 1 from contact where id=12)

- StartupGuy

---

除了上述建议，你还可以为返回的列命名，以便更方便地进行引用。例如：select exists(select 1 from contact where id=12) AS "exists"。 - Rowan

这样会更好，因为它总是会返回一个值（true或false），而不是有时返回None（这取决于你使用的编程语言），这可能无法按照你期望的方式扩展。 - isaaclw

我有一张拥有三千万行的数据库表格，但当我使用 exists 或者 limit 1 时，性能会急剧下降，因为Postgres会使用Seq扫描而不是索引扫描。即便使用 analyze 也没用。 - FiftiN

在这里的子查询中使用 limit 1 会帮助还是减慢查询速度？ - maciek

@maciek，请理解“id”是主键，因此“LIMIT 1”是无意义的，因为只有一个记录具有该id。 - Michael M

Answer 2

那么简单地说：

select 1 from tbl where userid = 123 limit 1;

上面的查询将根据给定的userid，返回空集或单行记录。

如果这个查询速度太慢，您可以考虑在tbl.userid上创建索引。

如果批处理中存在表中的任何一行，那么我就不必插入我的行，因为我知道它们都已经插入了。

为了保证即使程序在批次执行过程中被中断也能保持这一点，我建议您确保适当地管理数据库事务（即在单个事务中插入整个批次）。

- NPE

---

有时候，从程序角度来说，“select count(*) from (select 1 ... limit 1)”更容易实现，因为它保证始终会返回一行计数值为0或1的结果。 - David Aldridge

@DavidAldridge count(*) 仍意味着必须读取所有行，而 limit 1 则在第一条记录处停止并返回结果。 - Imraan

@Imraan，我认为你误解了这个查询。COUNT作用于一个嵌套的SELECT，该SELECT最多只有1行（因为LIMIT在子查询中）。 - jpmc26

Answer 3

INSERT INTO target( userid, rightid, count )
  SELECT userid, rightid, count 
  FROM batch
  WHERE NOT EXISTS (
    SELECT * FROM target t2, batch b2
    WHERE t2.userid = b2.userid
    -- ... other keyfields ...
    )       
    ;

顺便说一句：如果你想让整个批处理在出现重复时失败（假设有一个主键约束），那么

INSERT INTO target( userid, rightid, count )
SELECT userid, rightid, count 
FROM batch
    ;

这将完全按照您的要求执行：它要么成功，要么失败。

- wildplasser

---

这将检查每一行。他想进行单个检查。 - JNK

不，它只进行一次检查。子查询是不相关的。一旦找到一个匹配对，它就会退出。 - wildplasser

没错，我也以为它指的是外部查询。你加一分。 - JNK

顺便提一下：由于查询在事务内部，如果插入重复的ID，则不会发生任何事情，因此可以省略子查询。 - wildplasser

嗯，我不确定我理解了。在权限插入之后，我开始递减计数列。（只是一些图片的细节）如果行已经存在并且省略子查询，我认为我会遇到重复唯一键引发的错误，是吗？（userid和right形成该唯一键） - Valentin Kuzub

Answer 4

select true from tablename where condition limit 1;

我相信这就是Postgres用于检查外键的查询语句。

在您的情况下，您也可以一次完成此操作：

insert into yourtable select $userid, $rightid, $count where not (select true from yourtable where userid = $userid limit 1);

- Royce

Answer 5

SELECT 1 FROM user_right where userid = ? LIMIT 1

如果你的结果集包含一行，则无需插入。否则，插入你的记录。

- Fabian Barney

---

如果一批数据包含100行，它会返回给我100行，你认为这样好吗？ - Valentin Kuzub

您可以将其限制为1行。应该会表现更好。查看@aix编辑后的答案。 - Fabian Barney

Answer 6

如果你考虑性能，也许可以在函数中使用“PERFORM”来实现，就像这样：

 PERFORM 1 FROM skytf.test_2 WHERE id=i LIMIT 1;
  IF FOUND THEN
      RAISE NOTICE ' found record id=%', i;  
  ELSE
      RAISE NOTICE ' not found record id=%', i;  
 END IF;

- francs

---

无法使用：我在perform附近得到了语法错误。 - Simon

这是PL/pgsql，而不是SQL，如果尝试将其作为SQL运行，则会出现“PERFORM”语法错误。 - Mark K Cowan

Answer 7

正如 @MikeM 指出的。

select exists(select 1 from contact where id=12)

在联系人上使用索引，通常可以将时间成本降低到1毫秒。

CREATE INDEX index_contact on contact(id);

- hcnak

---

1毫秒的成本非常高 - 每秒只能进行1000次这样的检查。应该瞄准每秒约10M次检查。 - Meglio