Ulitization IS DISTINCT FROM - Postgres

Question

What is the best use of this function in Postgres IS DISTINCT FROM, doing tests obtained the same result using COALESCE but in less time, follows 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' -- teste com COALESCE, média de 610 ms

  (CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL) -- teste com OR, média de 668 ms

  CL.tp_pessoa IS DISTINCT FROM 'JURIDICA' -- teste com IS DISTINCT FROM, média de 667 ms

  OUTRO TESTE:

  COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') -- teste com IS DISTINCT FROM, média de 940 ms

  CL.tp_pessoa IS DISTINCT FROM P.observacao -- teste com ```IS DISTINCT FROM```, média de 930 ms, aqui teve uma leve vantagem da função

In addition to the lower performance, it is a function that is not found in other banks like the SQL Server, another reason not to use it.

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

Answer 1

The IS DISTINCT FROM comparison returns true when the data is different, the difference is that it treats null as a known value.

It can be used whenever you want to compare values and ignore null values.

For more details see documentation of Postgres.