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

>
> That's not too surprising. PostgreSQL can't choose a plan based on
> the parameter value when it doesn't know the parameter value

I thought that since 9.2, postgresql could "generate plans based on the
parameter value even when using prepared statements" (paraphrase of 9.2
release notes). I'm running version 10. That's why I was surprised to find
the different plans. If you're right that taking the value into account
causes the planner to guess wrong, I agree that's a separate issue -- but
is that really what's going on?

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

The queries included in the output (after "Query Text:"), which is why I
didn't include them separately.

> I think the deeper problem here may be that the planner has no idea
> what value uid_langvar() will return, so its selectivity estimates are
> probably fairly bogus. If you looked up that id first and then
> searched for the resulting value, it might do better.
>

I was under the impression, possibly incorrect, that the planner would
sometimes or always call a stable/immutable function in the planning stage
in order to consider its return value for planning. RhodiumToad on
#postgresql mentioned that functions returning constant values will be
folded in. He thought the planner should call uid_langvar() even though it
wasn't constant. Changing it from stable to immutable makes no difference,
and neither does reducing the cost to 10. Looking up the id first is an
obvious option but I thought there was a way to do this within a single
query. I guess not?

In any case, I still don't understand why prepared vs. not makes a
difference.

Dave