Stop using "IS NOT DISTINCT FROM"

[CaucaCG]

Performance issue due to the query generation on update.
It's using "IS NOT DISTINCT FROM" instead of "="
I don't think there's any reason to not use the "=" on not nullable field

[roji]

Can you provide more detail? Do you have any reason to believe "IS NOT DISTINCT FROM" is slower than "=" in PostgreSQL for non-nullable fields?

[CaucaCG]

When I run the query with "IS NOT DISTINCT FROM", it took 600ms, then I replace for "=" and it is only 1ms

[roji]

Can you please send the two queries which demonstrate this, including a schema?

[CaucaCG]

I've made some test with a new table having only two fields, both uuid. If I run the two queries with only few records, it took the same time. When adding 1,000,000 records then I'm starting having performance issue : 10ms vs 87ms.
Then I've added 500,000 more records and it's now 10ms vs 143ms

UPDATE mytable SET parentid='00000000-0000-0000-0000-000000000000' where id iS NOT DISTINCT FROM '015bf336-74a8-4bbd-b4cc-ecdc1fdbe996'
UPDATE mytable SET parentid='00000000-0000-0000-0000-000000000000' where id = '015bf336-74a8-4bbd-b4cc-ecdc1fdbe996'

[roji]

OK, I'll take a look at this.

[scolemann]

Another issue is that "IS NOT DISTINCT FROM" will not use an index. Here is a thread from the pgsql-hackers mailing list that discusses it.

http://postgresql.nabble.com/IS-NOT-DISTINCT-FROM-Indexing-td5812296.html

We are running into this issue as well and have update queries timing out because of it. In our case a simple equality would work and would use the index.

[roji]

Thanks for the thread @scolemann, this definitely explains the performance difference @CaucaCG noted above.

I'll make this change soon.

[roji]

Note: I originally used IS NOT DISTINCT FROM as a workaround for dotnet/efcore#3023

[roji]

So recalling the original problem, this is actually more of an EFCore issue than an Npgsql one - they actually generate good null comparisons for queries but not for updates. That's why I "hacked" things to use IS [NOT] DISTINCT FROM. dotnet/efcore#3023 is about fixing this in EFCore, have just posted there and let's wait to see their response.

I really hope we can avoid implementing doing null comparison logic ourselves here.

[scolemann]

@roji thanks for the explanation and sorting this out.

[scolemann]

Hi @roji,

It looks like the EF team fixed issue 3023 (Update Pipeline: Use C# null semantics for WHERE clauses). Is there any chance you could remove that IS NOT DISTINCT FROM code soon?

Thanks for your help!

[roji]

Thanks for telling me @scolemann, that somehow slipped under my radar!

I've removed the IS NOT DISTINCT FROM workaround. Unfortunately it seems there are some outstanding test failures because of the port to rc3, I'll try to solve those ASAP so you can get a working CI version. You can also clone and build yourself, it should be pretty easy.

[scolemann]

Awesome, thank you!