为什么要使用IS DISTINCT FROM - Postgres

Question

Postgres中，IS DISTINCT FROM函数的最佳用途是什么？经过测试发现使用COALESCE可以在更短的时间内获得相同的结果。

SELECT COUNT(P.id)

FROM produto P
  INNER JOIN cliente CL ON P.id_cliente = CL.id_cliente

WHERE 
  COALESCE(CL.tp_pessoa,'') <> 'JURIDICA' -- test with COALESCE, average 610 ms

  (CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL) -- test with OR, average 668 ms

  CL.tp_pessoa IS DISTINCT FROM 'JURIDICA' -- test with IS DISTINCT FROM, average 667 ms

  OUTRO TESTE:

  COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') -- test with IS DISTINCT FROM, average 940 ms

  CL.tp_pessoa IS DISTINCT FROM P.observacao -- test with ```IS DISTINCT FROM```, average 930 ms, a little beter here

它具有较低的性能，而且是其他数据库（如SQL Server）中没有的功能，这是不使用它的另一个原因。

进行另一个测试，在该测试中两个条件都可以是NULL时，IS DISTINCT FROM略有优势，这将是其使用的情况，更适用于哪里？

编辑：

像@hvd所说的，IS DISTINCT FROM是ANSI SQL的一部分，此外，COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'')的结果与CL.tp_pessoa IS DISTINCT FROM P.observacao不同。

- Tiago Oliveira de Freitas

---

“而且这是其他数据库中没有的函数” - 是的，但值得一提的是它是 ANSI SQL 的一部分，因此其他数据库不包括它可能被视为那些其他数据库的问题。 - user743382

@hvd，@Gordon Linoff，@Bacon Bits，@Gabriel's Messanger：对于我的测试，我正在比较tp_pessoa不应该是什么，在这种情况下是否可以使用index来协助？ - Tiago Oliveira de Freitas

Answer 1

你所看到的性能差异是微不足道的。专注于正确性。

你举了一个例子。

COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'')

对比

CL.tp_pessoa IS DISTINCT FROM P.observacao

如果CL.tp_pessoa是NULL，而P.observacao是''，那么第一个比较将把它们视为相等，而第二个比较将把它们视为不相等。

因此，如果您想将它们作为相等进行比较，请使用第一个版本；如果您想将它们作为不相等进行比较，请使用第二个版本。

- user743382

Answer 2

< p >使用COALESCE()的问题在于它会使子句无法被 SARG 优化。这将影响性能，因为索引无法使用。

(CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL)的问题在于它过于冗长，特别是当你要比较两个字段可能都为空时：

(CL.tp_pessoa <> CL2.tp_pessoa 
    OR (CL.tp_pessoa IS NOT NULL AND CL2.tp_pessoa IS NULL) 
    OR (CL.tp_pessoa IS NULL AND CL2.tp_pessoa IS NOT NULL))

如果你想使用 UPDATE 命令将临时表与基本表合并，并且它们共享 30 个字段，但是只有当至少一个字段不同且任何 29 个非关键字段都可以为空时才更新，你会发现编写查询语句多么麻烦。

- Bacon Bits

Answer 3

首先，它很方便。其次，您需要在更大量的数据上运行测试。在数据库服务器上一秒钟内可能发生很多事情，所以百分之几秒的微小变化并不一定表明整体性能。

从积极的方面来看，我认为Postgres将使用索引进行“不同于”的操作。我认为并非所有替代方法都必须使用索引。

- Gordon Linoff

---

在我的本地机器上对更大的银行进行了进一步的测试，因此没有软件竞争，差异再次很小，为2230-2240毫秒，因此性能不是问题。 - Tiago Oliveira de Freitas

Answer 4

如果像这样创建索引： CREATE INDEX "index_name" ON table1 USING btree (column1); 并且在SQL引擎使用IS DISTINCT FROM时，可以使用该索引。但是，在使用COALESCE时则无法使用该索引。

- Gabriel's Messanger