Обсуждение: raising the default prepareTheshold
I'd like to raise the default prepareThreshold to some non-zero value, perhaps 5 or so. This means that PreparedStatements that are used more than 5 times will allocate and use a named backend statement. Usually, using a named statement is a performance win as it avoids reparsing and replanning the query on each execution; I've seen measurable improvement (at least 15%, from memory) on heavily-reused simple queries where the parse/plan cost is a noticable fraction of total execution time. However, with a 8.0 backend, if the planner benefits from knowing the particular parameter values in use for a particular query, then using an unnamed statement may be faster due to using an improved plan. Under 7.4 there will be no difference; both named and unnamed statements will use the generic plan. So using named statements always by default (default prepareThreshold = 1) probably isn't a good idea. Using a default prepareThreshold > 1 seems like a reasonable compromise. What does everyone think? -O
On Sun, 19 Sep 2004, Oliver Jowett wrote: > I'd like to raise the default prepareThreshold to some non-zero value, > perhaps 5 or so. This means that PreparedStatements that are used more > than 5 times will allocate and use a named backend statement. > Seems like a good idea and I think five is a nice conservative number. At the same time do you think we should change the default fetchSize to 1000 or so? Even though it only works for FORWARD_ONLY ResultSets it could reduce the number of complaints we see here without much penalty. Previously the cursor implementation was more limiting by ruling out server prepared statements. Kris Jurka
Hi, Oliver, On Sun, 19 Sep 2004 09:29:04 +1200 Oliver Jowett <oliver@opencloud.com> wrote: > However, with a 8.0 backend, if the planner benefits from knowing the > particular parameter values in use for a particular query, then using an > unnamed statement may be faster due to using an improved plan. Under 7.4 > there will be no difference; both named and unnamed statements will use > the generic plan. Quoting from http://developer.postgresql.org/beta-history.txt: * Plan prepared queries only when first executed so constants can be used for statistics (Oliver Jowett) Prepared statements plan queries once and execute them many times. While prepared queries avoid the overhead of re-planning on each use, the quality of the plan suffers from not knowing the exact parameters to be used in the query. In this release, planning of unnamed prepared statements is delayed until the first execution, and the actual parameter values of that execution are used as optimization hints. So it seems that your issue is addressed in 8.0 - as long as the Beta of the Readme is correct :-) HTH, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com
Markus Schaber wrote: > Hi, Oliver, > > On Sun, 19 Sep 2004 09:29:04 +1200 > Oliver Jowett <oliver@opencloud.com> wrote: > > >>However, with a 8.0 backend, if the planner benefits from knowing the >>particular parameter values in use for a particular query, then using an >>unnamed statement may be faster due to using an improved plan. Under 7.4 >>there will be no difference; both named and unnamed statements will use >>the generic plan. > > > Quoting from http://developer.postgresql.org/beta-history.txt: > > * Plan prepared queries only when first executed so constants can be > used for statistics (Oliver Jowett) > Prepared statements plan queries once and execute them many times. > While prepared queries avoid the overhead of re-planning on each > use, the quality of the plan suffers from not knowing the exact > parameters to be used in the query. In this release, planning of > unnamed prepared statements is delayed until the first execution, > and the actual parameter values of that execution are used as > optimization hints. Yes, this is the end result of the patch I submitted. > So it seems that your issue is addressed in 8.0 - as long as the Beta of > the Readme is correct :-) The readme is right (although the first sentence is a bit general). However, it's not a perfect solution. If you use named statements then the query is planned only once, when the Parse message is received. This plan cannot take the actual parameter values being used into account. In contrast, the unnamed statement is planned when the first Bind is received, and uses the actual parameter values for cost estimation. So using a named statement might produce a worse plan than using the unnamed statement each time. By default, the JDBC driver uses the unnamed statement for each query execution, which should produce the same plans as if it just did direct parameter substitution into the query string. If the prepareThreshold is reached for a particular PreparedStatement, the JDBC driver starts (re)using a named statement for that query, which is not *always* a win. It does avoids re-parsing and re-planning the query each time, but each execution may run slower if the plan becomes worse. -O
Kris Jurka wrote: > > At the same time do you think we should change the default fetchSize to > 1000 or so? Even though it only works for FORWARD_ONLY ResultSets it > could reduce the number of complaints we see here without much penalty. > Previously the cursor implementation was more limiting by ruling out > server prepared statements. Sounds like a good idea. -O
On Mon, 20 Sep 2004, Kris Jurka wrote: > At the same time do you think we should change the default fetchSize to > 1000 or so? Even though it only works for FORWARD_ONLY ResultSets it > could reduce the number of complaints we see here without much penalty. > Previously the cursor implementation was more limiting by ruling out > server prepared statements. > Actually a little testing shows that a non-zero fetchSize implies using a named statement instead of the unnamed statement. So there will be some potentially negative planning differences, so I'm going to retract this suggestion. Kris Jurka
Kris Jurka wrote: > > On Mon, 20 Sep 2004, Kris Jurka wrote: > > >>At the same time do you think we should change the default fetchSize to >>1000 or so? Even though it only works for FORWARD_ONLY ResultSets it >>could reduce the number of complaints we see here without much penalty. >>Previously the cursor implementation was more limiting by ruling out >>server prepared statements. >> > > > Actually a little testing shows that a non-zero fetchSize implies using a > named statement instead of the unnamed statement. So there will be some > potentially negative planning differences, so I'm going to retract this > suggestion. Ah, this is because if we used the unnamed statement, the created portal gets closed when the unnamed statement is reused. I forgot about that. -O