Re: Index not used without explicit typecast

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

Get Outlook for Android

---

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