Re: Plan for update ... where a is not distinct from b

Peter J. Holzer wrote:
> I noticed that an update was taking a long time and found this:
>
> UPDATE public.facttable_imf_ifs p
> SET [...lots of columns...]
> FROM cleansing.cls_imf_ifs_facttable_imf_ifs c, cleansing.cleansing_change_type ct
> WHERE
> (p.macrobondtimeseries is not distinct from c.macrobondtimeseries) AND (p.date is not distinct from c.date)
> AND c.cleansing_change_type_id = ct.cleansing_change_type_id
> AND ct.cleansing_change_type_desc_short IN ('UPDATED_NEW')

It is kind of ugly, and I didn't test it, but here is an idea:

Suppose we know a value that cannot occur in both p.date and c.date.

Then you could write

WHERE coalesce(p.date, '0044-03-15 BC') = coalesce(c.date, '0044-03-15 BC')

and create an index on the coalesce expressions to facilitate
a merge join.

Yours,
Laurenz Albe