问题描述
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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!