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