Re: [HACKERS] FE/BE protocol vs. parameterized queries
Tom Lane wrote: The infrastructure for the former planning method (using the first Bind's parameters as sample values for estimation, but not as constants) is still there, but it's not being used now. Does anyone want to argue for changing things to plan named statements that way? I'm of two minds about it myself; you can make a good case that it'd usually be a win, but it's also not hard to envision scenarios where it'd be a loss. Although I don't have a clear opinion myself, I sometimes read on this list that people are using prepared statements to get safe, stable plans, i.e. plans that don't depend on the specific parameter input. If you change that, I don't think they will be happy at all. I suggest leaving it as-is for 8.2. I think the user (i.e. driver) should be able to tell the backend, if they want planning for the first bind, or right at prepare. Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FE/BE protocol vs. parameterized queries
Although I don't have a clear opinion myself, I sometimes read on this list that people are using prepared statements to get safe, stable plans, i.e. plans that don't depend on the specific parameter input. I definitely want the possibility of getting stable plans. That's only possible if the planner does NOT take into account any parameter values. If the statistics get quicker out of date than it's practical to run analyze, but the plans would stay stable, it's better not to have parameter values taken into account. If you change that, I don't think they will be happy at all. I suggest leaving it as-is for 8.2. I think the user (i.e. driver) should be able to tell the backend, if they want planning for the first bind, or right at prepare. That would be nice. We would probably use all 3 forms: - unnamed statement: prepare based on constant parameters; - named statement: prepare based on the first set of parameter values; - named statement: prepare generic plan without considering parameter values; Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] FE/BE protocol vs. parameterized queries
I wrote: Andrew - Supernews [EMAIL PROTECTED] writes: I believe this could usefully (and transparently to clients) be changed so that Bind on the unnamed statement does _not_ store the plan back in the unnamed statement's context, but instead produces a plan which is only used _for that specific portal_. That seems OK to me, since we document the unnamed statement/portal as being optimized for one-shot execution. Unfortunately it's probably less than a trivial change, because the planner never assumes that Params are constants; that would have to be changed somehow. I've applied a patch to do this --- the planner change turned out to be pretty trivial after all. The infrastructure for the former planning method (using the first Bind's parameters as sample values for estimation, but not as constants) is still there, but it's not being used now. Does anyone want to argue for changing things to plan named statements that way? I'm of two minds about it myself; you can make a good case that it'd usually be a win, but it's also not hard to envision scenarios where it'd be a loss. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] FE/BE protocol vs. parameterized queries
Picking out a specific point from the thread on prepared queries: Currently, the handling of Parse/Bind on the unnamed statement seems to go like this: - Parse on the unnamed statement does analysis and rewriting but does not plan, storing the query in a special memory context dedicated to the unnamed statement - Bind on the unnamed statement plans the query (using the supplied parameters) and stores the plan back in the unnamed statement's context I believe this could usefully (and transparently to clients) be changed so that Bind on the unnamed statement does _not_ store the plan back in the unnamed statement's context, but instead produces a plan which is only used _for that specific portal_. Thus, it would promote the parameters to constants before planning, knowing that the plan could only be run once; this would, I believe, allow the planner to produce a plan that was equivalent to that of a non-parameterized query. This would hopefully remove all cases where it is currently necessary to use PQexec rather than PQexecParams, such as where parameterized limits, immutable functions of parameters, partial indexes etc. are involved. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FE/BE protocol vs. parameterized queries
Andrew - Supernews [EMAIL PROTECTED] writes: I believe this could usefully (and transparently to clients) be changed so that Bind on the unnamed statement does _not_ store the plan back in the unnamed statement's context, but instead produces a plan which is only used _for that specific portal_. That seems OK to me, since we document the unnamed statement/portal as being optimized for one-shot execution. Unfortunately it's probably less than a trivial change, because the planner never assumes that Params are constants; that would have to be changed somehow. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
