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 DISTINCT FROM is part of ANSI SQL, also, the result of COALESCE(CL.tp_pessoa,'') <> COALESCE(P.observacao,'') is not the same of CL.tp_pessoa IS DISTINCT FROM P.observacao.

"and is a function that is not found in other DBs" -- True, but worth mentioning is that it is part of ANSI SQL, so the fact that other DBs don't include it is something others might see as a problem with those other DBs. — user743382
– user743382, Commented Nov 20, 2015 at 13:59
@hvd, @Gordon Linoff, @Bacon Bits, @Gabriel's Messanger: For my tests , I'm comparing what tp_pessoa should NOT be , using index help in this case ? — Tiago Oliveira de Freitas
– Tiago Oliveira de Freitas, Commented Nov 20, 2015 at 20:03

user743382 · Accepted Answer · 2015-11-20 14:01:48Z

20

The performance differences you've seen are minimal. Focus on correctness.

You give an example

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

versus

CL.tp_pessoa IS DISTINCT FROM P.observacao

If CL.tp_pessoa is NULL, and P.observacao is '', then the first comparison treats them as equal, whereas the second comparison treats them as unequal.

So use the first version if you want to compare them as equal, and the second version if you want to compare them as unequal.

answered Nov 20, 2015 at 14:01

user743382

Sign up to request clarification or add additional context in comments.

Comments

mksm · Accepted Answer · 2023-11-20 15:16:48Z

8

The problem with COALESCE() is that it makes the clause non-SARGable. That will impact performance because indexes can't be used.

The problem with (CL.tp_pessoa <> 'JURIDICA' OR CL.tp_pessoa IS NULL) is that it's verbose, especially when you're looking at comparing two fields that might both be 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))

If you're doing an UPDATE to merge a temp table with your base table and they share 30 fields and you only want to update when at least one of the fields is different and any of the 29 non-key fields can be NULL... you can understand how much of a pain it can be to write the query.

edited Nov 20, 2023 at 15:16

mksm

4,6175 gold badges41 silver badges57 bronze badges

answered Nov 20, 2015 at 14:02

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2015-11-20 13:54:35Z

5

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.

answered Nov 20, 2015 at 13:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Tiago Oliveira de Freitas Over a year ago

Did further tests on a larger bank, on my local machine , so no software competition , and the difference was minimal again 2230 - 2240 ms , then performance is no problem

Gabriel's Messanger · Accepted Answer · 2015-11-20 13:55:38Z

2

If there is a index on column1 like CREATE INDEX "index_name" ON table1 USING btree (column1); then in case of IS DISTINCT FROM sql engine can use thet index. With COALESCE it can't.

answered Nov 20, 2015 at 13:55

Gabriel's Messanger

3,37822 silver badges32 bronze badges

Collectives™ on Stack Overflow

Why use IS DISTINCT FROM - Postgres

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related