Merging with null matching causes extreme performance degradation.

[brendan-cook-87]

Describe the bug

When we add the join clause OR (target."id" IS NULL AND source."id" IS NULL) to enable matching of nulls in the id columns, it causes some queries to take orders of magnitude longer to complete.

#2872

How to Reproduce

I have a table with approximately 17 million rows. And the approximate data scanned for an insert operation is 350MB.

The previous behaviour of running:
MERGE INTO "production_mobile"."tracks" target USING "production_mobile"."temp_table_f30f8023428b4dab816840e62ba40699" source ON (target."id" = source."id")

to insert ~300 new rows would execute in around 5s.

Running it with this clause:
MERGE INTO "production_mobile"."tracks" target USING "production_mobile"."temp_table_f30f8023428b4dab816840e62ba40699" source ON (target."id" = source."id" OR (target."id" IS NULL AND source."id" IS NULL)

is taking 12+ minutes across several attempts.

This table has no nulls in the id column. It is processing event logs and merging on UUIDs.

Expected behavior

This behaviour should not be the default given that it causes live production systems such a degradation in performance.
I am unable to update to the latest version of this layer at this time.

Your project

No response

Screenshots

No response

OS

Mac

Python version

3.11

AWS SDK for pandas version

3.9.0

Additional context

No response

[aldder]

Hello Bendan, sorry to hear that my pr introduced this huge delay in timings.

As an alternative to the control check

f'(target."{x}" = source."{x}" OR (target."{x}" IS NULL AND source."{x}" IS NULL))'

can you please try the following:

f'(target."{x}" IS NOT DISTINCT FROM source."{x}"'

Looking at the documentation https://trino.io/docs/current/functions/comparison.html#is-distinct-from-and-is-not-distinct-from this is the the proper way to have some sort of equality check on NULL values, but we must be aware that this doesn't work if any of the columns involved contains ALL NULL values (due to a bug on trino, idk honestly).

Please let me know.

[brendan-cook-87]

I just ran a test using IS NOT DISTINCT FROM and it still took around 8 minutes to do the insert.
So I'd agree it seems slightly more performant, but still orders of magnitude slower than just merging on =.

[Siddharth-Latthe-07]

@brendan-cook-87 The drastic increase in query execution time when adding the OR (target."id" IS NULL AND source."id" IS NULL) is due to how SQL databases handle null comparisons and the increased complexity of the query.
Try out these steps and let me know, if it works

1. Ensure No Nulls:
   If your table has no nulls in the id column, you don't need to add the null-check condition.
   refer this query:

MERGE INTO "production_mobile"."tracks" target 
USING "production_mobile"."temp_table_f30f8023428b4dab816840e62ba40699" source 
ON (target."id" = source."id")

2. Use Coalesce for Null Handling: (if applicable)
   If you need to handle potential nulls in a different context, you can use COALESCE to provide a default value for nulls.
3. Partition the Data: If you need to handle a significant number of rows, consider partitioning your data to improve performance.
4. Optimize Indexes: Ensure that your indexes are optimized and consider adding composite indexes if applicable.
   for optimiztion refer this query:-

MERGE INTO "production_mobile"."tracks" target 
USING "production_mobile"."temp_table_f30f8023428b4dab816840e62ba40699" source 
ON (COALESCE(target."id", 'default_value') = COALESCE(source."id", 'default_value'))

Hope, this helps ad let me know the further issues
Thanks

[brendan-cook-87]

I agree merging on id = id works fine. That's exactly why I raised the issue.
Because the latest version changes the sql generated by to_iceberg to the less efficient version...