Why use IS DISTINCT FROM - Postgres

Question

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.

Answer 1

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.