Обсуждение: domain cast in parameterized vs. non-parameterized query

On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz <lautgesetz@gmail.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

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Dec 20, 2017 at 1:03 AM, David Kamholz <lautgesetz@gmail.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

I wrote:
> 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.

Hm, scratch that --- experimentation shows that the parser still produces
a CoerceToDomain node in that case, not a literal of the domain type.

regression=# create domain foo as text;
CREATE DOMAIN
regression=# explain verbose select 'x'::foo;               
                QUERY PLAN                 
-------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32)
   Output: ('x'::text)::foo
(2 rows)

You could force the issue with an immutable function:

regression=# create function forcefoo(text) returns foo as
regression-# 'begin return $1::foo; end' language plpgsql immutable;
CREATE FUNCTION
regression=# explain verbose select forcefoo('x');
                QUERY PLAN                 
-------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=32)
   Output: 'x'::foo
(2 rows)

Marking this function as immutable is sort of a lie, because it
is effectively telling the planner that you don't expect any
failure from pre-evaluation of the function.  But it'd get the
job done, and in most situations there's no practical difference
because any failure would have happened anyway at runtime.

            regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2017-12-20 23:41 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> Hm, scratch that --- experimentation shows that the parser still produces
>> a CoerceToDomain node in that case, not a literal of the domain type.

> Why the rewrite doesn't reduce it? Or why parser does it?

Because ALTER DOMAIN can change what would be a valid value.

regression=# create domain myd as int;
CREATE DOMAIN
regression=# create view v1 as select 0::myd as c1;
CREATE VIEW
regression=# select * from v1;
 c1 
----
  0
(1 row)

regression=# alter domain myd add check (value > 0);
ALTER DOMAIN
regression=# select * from v1;
ERROR:  value for domain myd violates check constraint "myd_check"

If the view's expression had been reduced to just a Const when it
was stored, we'd not notice that the value is no longer valid for
the domain.  So CoerceToDomain is always postponed till runtime.

            regards, tom lane

David Kamholz <lautgesetz@gmail.com> writes:
> In the view case that makes sense, but I don't see how ALTER DOMAIN is
> relevant to my original example. You can't alter a domain between the time
> the query is parsed and executed, can you?

Yes, you can.  Especially so for prepared statements.

            regards, tom lane