Any statement that is executed has to go through the 4 stages of query execution: - parse - rewrite - plan - execute
The execute phase is the phase that mostly is the focus on, and is the phase in which normally is spent the most time. In the postgres backend main loop, there are multiple ways of getting a statement to go through these stages. The simple query execution is a single call that performs going through all these stages and the other common method is to use the client parse (which includes the server side parse and rewrite), bind (which performs the server side plan) and execute commands from this backend main loop. A prepared statement, or named statement, is a way of performing statement execution where some of the intermediate results are stored in a memory area in the backend and thus allows the backend to persist some of the execution details. Non-prepared statement reuse the memory area, and thus flush any metadata. The reason for explaining this is that when preparing a statement, the result of the phases of parse and rewrite, which is the parse tree, is stored. That means that after the prepare, the work of generating the parse tree can be omitted by only performing calling bind and execute for the prepared/named statement. The planner statistics are recorded for the calculated cost of a statement with the specified variables/binds, and record a cost of when the specified binds would be “non specific” alias generic. After 5 times of execution of a prepared statement, if the generic plan is costed equal or lower during than the plan of the statement with the specified bind variables, then the backend will switch to the generic plan. The advantage of switching to the generic plan is that it will not perform the plan costing and all accompanied transformations, but instead directly use the generic plan. For this question, this would ’solve’ the issue of the plan phase taking more time than the execution, but potentially only after 5 times of executing the prepared statement. The downside is that because the costing is skipped, it cannot choose another plan anymore for that named statement for the lifetime of the prepared statement in that backend, unless the backend is instructed explicitly to not to use the generic statement. Frits Hoogland > On 11 Sep 2023, at 10:13, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > On Mon, 2023-09-11 at 12:57 +0800, Mikhail Balayan wrote: >> Thanks for the idea. I was surprised to find that this is not the way it >> works and the planning time remains the same. > > To benefit from the speed gains of a prepared statement, you'd have to > execute it > at least seven times. If a generic plan is used (which should happen), you > will > see $1 instead of the literal argument in the execution plan. > > Prepared statements are probably your best bet. > > Yours, > Laurenz Albe > >
