Thread: BUG #1753: Query Optimizer does not work well with libpg / PQexecParams
The following bug has been logged online: Bug reference: 1753 Logged by: Ernst Bachmann Email address: e.bachmann@xebec.de PostgreSQL version: 8.0.3 Operating system: Linux Description: Query Optimizer does not work well with libpg / PQexecParams Details: It looks like the query optimizer isn't taking the value of parameters sent with PQexecParams into account, thus generating (in my case, very) unoptimal plans There is a warning about that for "PREPARE", but with PQexecParams, the parameter values should be available immediatly for the planner. Sending the parameters inline in the SQL query string solved the problem for me (Query executes in 5 ms instead of 500+), but thats obviously not my favorite solution. Is this something planned to be fixed, otherwise a warning in the libpq documentation about "PQexecParams" being potentially slow would be nice. Thanks /Ernst
Ernst Bachmann wrote: > The following bug has been logged online: > > Bug reference: 1753 > Logged by: Ernst Bachmann > Email address: e.bachmann@xebec.de > PostgreSQL version: 8.0.3 > Operating system: Linux > Description: Query Optimizer does not work well with libpg / > PQexecParams > Details: > > It looks like the query optimizer isn't taking the value of parameters sent > with PQexecParams into account, thus generating (in my case, very) unoptimal > plans If PQexecParams uses the unnamed statement (it appears to), this shouldn't happen -- planning of the unnamed statement is delayed until the first set of parameter values is bound. This behaviour started in 8.0. What's the query? Are you definitely using PQexecParams() and not PQexecPrepared()? -O
On 2005-07-05, Oliver Jowett <oliver@opencloud.com> wrote: > Ernst Bachmann wrote: >> The following bug has been logged online: >> >> Bug reference: 1753 >> Logged by: Ernst Bachmann >> Email address: e.bachmann@xebec.de >> PostgreSQL version: 8.0.3 >> Operating system: Linux >> Description: Query Optimizer does not work well with libpg / >> PQexecParams >> Details: >> >> It looks like the query optimizer isn't taking the value of parameters sent >> with PQexecParams into account, thus generating (in my case, very) unoptimal >> plans > > If PQexecParams uses the unnamed statement (it appears to), this > shouldn't happen -- planning of the unnamed statement is delayed until > the first set of parameter values is bound. This behaviour started in 8.0. The problem is that even with the unnamed statement and deferred planning, the planner still has to treat the parameters as variables, not constants, since nothing in the protocol stops you from running multiple portals from the unnamed statement. This can make a significant difference, especially where function calls are involved and major optimizations can be made on constant values as a result of inlining. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote: > The problem is that even with the unnamed statement and deferred planning, > the planner still has to treat the parameters as variables, not constants, > since nothing in the protocol stops you from running multiple portals from > the unnamed statement. This can make a significant difference, especially > where function calls are involved and major optimizations can be made on > constant values as a result of inlining. Sure, expression optimization is less aggressive, but is that on its own really going to produce a 100-fold difference in query execution? The main problem pre-8.0 (or with named statements) is that index selectivity estimates go out the window with a parameterized query, so a much more general (and slower) plan gets chosen. The 8.0 unnamed-statement behaviour glues the actual parameter values into the selectivity estimates so in theory you should get the same plan for the unparameterized and parameterized-unnamed-statement cases. This is why I'd like to see the actual query.. -O
On 2005-07-06, Oliver Jowett <oliver@opencloud.com> wrote: > Andrew - Supernews wrote: >> The problem is that even with the unnamed statement and deferred planning, >> the planner still has to treat the parameters as variables, not constants, >> since nothing in the protocol stops you from running multiple portals from >> the unnamed statement. This can make a significant difference, especially >> where function calls are involved and major optimizations can be made on >> constant values as a result of inlining. > > Sure, expression optimization is less aggressive, but is that on its own > really going to produce a 100-fold difference in query execution? Sure. Only for specific types of queries, of course. > The main problem pre-8.0 (or with named statements) is that index > selectivity estimates go out the window with a parameterized query, so a > much more general (and slower) plan gets chosen. The 8.0 > unnamed-statement behaviour glues the actual parameter values into the > selectivity estimates correct so far... > so in theory you should get the same plan for the unparameterized and > parameterized-unnamed-statement cases. But that doesn't follow, since selectivity estimation isn't the only factor. > This is why I'd like to see the actual query.. Yes, it would certainly help. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Oliver Jowett <oliver@opencloud.com> writes:
> Sure, expression optimization is less aggressive, but is that on its own
> really going to produce a 100-fold difference in query execution?
It's certainly possible, depending on query details. Personally, I
ignored the original report in this thread as being utterly content
free...
regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>Sure, expression optimization is less aggressive, but is that on its own >>really going to produce a 100-fold difference in query execution? > > > It's certainly possible, depending on query details. Andrew pointed out in some offlist discussion that it's actually more than just expression optimization via eval_const_expressions; there are some other cases where the tree is transformed differently if you have a Const vs. Param in ways that estimate_expression_value() doesn't deal with: - predicate_implied_by_simple_clause can remove parts of an expression based on the actual (not estimated) values involved; - match_special_index_operator manipulates LIKE/regexp/etc expressions based on the actual patterns provided; - LIMIT/OFFSET modifications to query cost only kick in with Consts. I can't see any simple way to fix the first two since they change the meaning of the expression, but the LIMIT/OFFSET query cost code could in theory use estimate_expression_value(). -O