Thanks for the quick reply and suggestions. I will change all references to integer type then.
Adding more indexes could be a good fallback if the other solution has unforeseen problems at the application side.
Regards,
Jan
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, August 5, 2020 4:32:22 PM
To: Jan Kort <jan.kort@genetics.nl>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Index not used without explicit typecast
Jan Kort <jan.kort@genetics.nl> writes:
> UPDATE TABLE1 SET date1 = current_timestamp WHERE ID = 1000000;
> [ uses index on integer column ID ]
> UPDATE TABLE1 SET date1 = current_timestamp WHERE ID = 1000000::numeric;
> [ doesn't use index ]
Yeah. This is the price we pay for extensibility. The only available
"=" operator that can match the second query is "numeric = numeric",
so the parser effectively converts it to "ID::numeric = 1000000::numeric",
and then "ID::numeric" does not match the index, any more than say
"abs(ID)" would.
In principle one could invent an "integer = numeric" operator and then
make it a member of the appropriate btree operator class, but there are
assorted pitfalls and gotchas in that. The biggest risk is that the
extra operator would result in "ambiguous operator" failures for queries
that work fine today.
If you're desperate for a workaround that doesn't involve fixing the
query, you could build an additional index on "ID::numeric". This'd
be kind of expensive from an index-maintenance standpoint, of course.
regards, tom lane