Re: Index not used without explicit typecast

Hi

st 5. 8. 2020 v 18:47 odesílatel Jan Kort <jan(dot)kort(at)genetics(dot)nl> napsal:

> 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.
>

Any other index increases the cost of INSERT, UPDATE or VACUUM.

Regards

Pavel

> Regards,
> Jan
>
> Get Outlook for Android <https://aka.ms/ghei36>
>
> ------------------------------
> *From:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> *Sent:* Wednesday, August 5, 2020 4:32:22 PM
> *To:* Jan Kort <jan(dot)kort(at)genetics(dot)nl>
> *Cc:* pgsql-bugs(at)lists(dot)postgresql(dot)org <pgsql-bugs(at)lists(dot)postgresql(dot)org>
> *Subject:* Re: Index not used without explicit typecast
>
> Jan Kort <jan(dot)kort(at)genetics(dot)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
>