为什么使用 IS DISTINCT FROM - Postgres [英] Why use IS DISTINCT FROM - Postgres

问题描述

Postgres IS DISTINCT FROM 中这个函数的最佳用途是什么，试听使用 COALESCE 得到了相同的结果，但时间更短，以下测试:

SELECT COUNT(P.id)FROM 产品 PINNER JOIN cliente CL ON P.id_cliente = CL.id_cliente在哪里COALESCE(CL.tp_pessoa,'') <>'JURIDICA' -- 使用 COALESCE 进行测试，平均 610 毫秒(CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL) -- 用 OR 测试，平均 668 毫秒CL.tp_pessoa IS DISTINCT FROM 'JURIDICA' -- 使用 IS DISTINCT FROM 进行测试，平均 667 毫秒外测:COALESCE(CL.tp_pessoa,'') <>COALESCE(P.observacao,'') -- 使用 IS DISTINCT FROM 进行测试，平均 940 毫秒CL.tp_pessoa IS DISTINCT FROM P.observacao -- 用 ```IS DISTINCT FROM``` 测试，平均 930 毫秒，这里稍微好一点

它的性能较低，是SQL Server等其他数据库中没有的功能，这是不使用它的另一个原因.

再做一个测试，其中两个条件都可以是 NULL ，IS DISTINCT FROM 有一点优势，这就是它的用途，哪里更适用?>

就像@hvd 所说的那样，它是 ANSI SQL 的一部分，并且是 COALESCE(CL.tp_pessoa,'') COALESCE(CL.tp_pessoa,'') <> 的结果.COALESCE(P.observacao,'') 与 CL.tp_pessoa IS DISTINCT FROM P.observacao 不同.

解决方案

第一，方便.其次，您需要对大量数据进行测试.一秒钟内，数据库服务器上可能会发生很多事情，因此百分之一秒的微小变化并不一定代表整体性能.

从积极的方面来说，我认为 Postgres 会为 与 不同而使用索引.我认为索引不一定会用于所有替代方案.

What is the best use of this function in Postgres IS DISTINCT FROM, auditioning got the same result using COALESCE but in less time , following the test :

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

Its have lower performance and is a function that is not found in other DBs such as SQL Server , another reason to not use it .

Doing another test, where both criteria can be NULL , the IS DISTINCT FROM had a slight advantage , this would be its use , where more it applies ?

Edit:

Like @hvd said is that it is part of ANSI SQL and the result of COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') is not the same of CL.tp_pessoa IS DISTINCT FROM P.observacao.

解决方案

First, it is convenient. Second, you need to run tests on larger amounts of data. A lot can happen on a database server in a second, so small changes in hundredths of a second are not necessarily indicative of overall performance.

On the positive side, I think Postgres will use an index for is distinct from. I don't think an index will necessarily be used for all the alternatives.

这篇关于为什么使用 IS DISTINCT FROM - Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！