Re: domain cast in parameterized vs. non-parameterized query

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz <lautgesetz(at)gmail(dot)com> wrote:
>> Note that in the above plan, 'spa-000' is cast to text before it's cast to uid. This
>> is apparently connected to why postgresql can't choose the better plan.

> It's slightly hard for me to follow what's going on with the
> auto_explain output you provided because you didn't specify what SQL
> you ran to produce that output, but I suspect that's not the case.

I think what's happening in the first case is that $1 is deemed to be
already of type uid, and there's a value available for it (viz,
'spa-000'::uid), so the planner constant-folds $1 to 'spa-000'::uid,
which is why we see the latter not the former in the plan output.
But then, since uid_langvar() is marked stable, we're allowed to
pre-evaluate it to produce an estimated value of the function result,
and the selectivity estimate for (langvar = uid_langvar('spa-000'::uid))
is done using that estimated value. This evidently leads us to conclude,
correctly, that very few rows will fail that filter condition; so we end
up picking a plan that relies primarily on the condition on id.

In the second case, what we're dealing with is evidently
('spa-000'::text)::uid), which has to be read carefully: that's a constant
of type text with a run-time cast to the domain. The planner is unwilling
to try to constant-fold CoerceToDomain, even speculatively, so it's left
with no way to get an estimated value for the uid_langvar() result,
leading to a default estimate for the selectivity of the langvar =
uid_langvar() condition. That default estimate is way off --- far too
optimistic --- so we make a poor choice of plan as a result.

You might consider whether you can write 'spa-000'::uid explicitly in your
query; that results in immediate application of the domain coercion, so
that the planner no longer sees that as a run-time operation it has to
avoid.

It's tempting to wonder whether we could somehow constant-fold
CoerceToDomain, at least in the estimation case, but I'm afraid that
would lead to domain constraint failures that would not necessarily occur
at runtime. Or we could skip the domain check for estimation purposes,
but then we're possibly feeding a value that fails the domain check to a
function that might not work right under such conditions. So on the
whole I'm afraid to monkey with that decision.

regards, tom lane