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

On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz <lautgesetz(at)gmail(dot)com> wrote:
> I've recently come across a query that produces different plans depending on
> whether it's parameterized or not.

That's not too surprising. PostgreSQL can't choose a plan based on
the parameter value when it doesn't know the parameter value, so the
only way it could get the same plan in both cases is if it ignored the
parameter value when it does know it, which would result in a lot of
really terrible plans. Knowing the parameter value tends to improve
the plan considerably, although apparently not in this case. Planning
is an inexact science and estimates are and actual numbers can vary,
so it can happen that the generic plan contains no bad estimate and
the parameter-specific plan does have a bad estimate.

> 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 the planner just has to guess whether it should scan the index
on exprx, taking advance of the fact that the ordering of that index
matches the desired output ordering of the the query, and hoping that
the join to expr will produce output rows fairly quickly so that the
whole nested loop will not have to be executed; or whether it should
instead using the index on expr, which lets it throw away all of the
rows where langvar doesn't have the right value to be interesting. In
the first strategy, we've got to probe expr for every value found in
exprx and some of the rows we find will have a langvar that causes us
to ignore them; the second strategy lets us immediately focus in on
the rows with the right langvar but requires a sort afterward.

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company