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

Поиск
Список
Период
Сортировка
От David Kamholz
Тема Re: domain cast in parameterized vs. non-parameterized query
Дата
Msg-id CAKuxgJ5DhvcFC7ZWbao7iFHWfxr2pU5X4XC8dEHdxsZDf5cxJw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: domain cast in parameterized vs. non-parameterized query  (Robert Haas <robertmhaas@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Robert Haas
Дата:
Сообщение: Re: domain cast in parameterized vs. non-parameterized query
Следующее
От: Robert Haas
Дата:
Сообщение: Re: Bitmap table scan cost per page formula