Re: 7.3 schedule - Mailing list pgsql-hackers

Neil Conway wrote:
> On Thu, 11 Apr 2002 16:25:24 +1000
> "Ashley Cambrell" <ash@freaky-namuh.com> wrote:
> 
>>What are the chances that the BE/FE will be altered to take advantage of 
>>prepare / execute? Or is it something that will "never happen"?
> 
> 
> Is there a need for this? The current patch I'm working on just
> does everything using SQL statements, which I don't think is
> too bad (the typical client programmer won't actually need to
> see them, their interface should wrap the PREPARE/EXECUTE stuff
> for them).
> 

Yes there is a need.

If you break up the query into roughly three stages of execution:
parse, plan, and execute, each of these can be the performance 
bottleneck.  The parse can be the performance bottleneck when passing 
large values as data to the parser (eg. inserting one row containing a 
100K value will result in a 100K+ sized statement that needs to be 
parsed, parsing will take a long time, but the planning and execution 
should be relatively short).  The planning stage can be a bottleneck for 
complex queries.  And of course the execution stage can be a bottleneck 
for all sorts of reasons (eg. bad plans, missing indexes, bad 
statistics, poorly written sql, etc.).

So if you look at the three stages (parse, plan, execute) we have a lot 
of tools, tips, and techniques for making the execute faster.  We have 
some tools (at least on the server side via SPI, and plpgsql) to help 
minimize the planning costs by reusing plans.  But there doesn't exist 
much to help with the parsing cost of large values (actually the 
fastpath API does help in this regard, but everytime I mention it Tom 
responds that the fastpath API should be avoided).

So when I look at the proposal for the prepare/execute stuff:
PREPARE <plan> AS <query>;
EXECUTE <plan> USING <parameters>;
DEALLOCATE <plan>;

Executing a sql statement today is the following:
insert into table values (<stuff>);
which does one parse, one plan, one execute

under the new functionality:
prepare <plan> as insert into table values (<stuff>);
execute <plan> using <stuff>;
which does two parses, one plan, one execute

which obviously isn't a win unless you end up reusing the plan many 
times.  So lets look at the case of reusing the plan multiple times:
prepare <plan> as insert into table values (<stuff>);
execute <plan> using <stuff>;
execute <plan> using <stuff>;
...
which does n+1 parses, one plan, n executes

so this is a win if the cost of the planing stage is significant 
compared to the costs of the parse and execute stages.  If the cost of 
the plan is not significant there is little if any benefit in doing this.

I realize that there are situations where this functionality will be a 
big win.  But I question how the typical user of postgres will know when 
they should use this functionality and when they shouldn't.  Since we 
don't currently provide any information to the user on the relative cost 
of the parse, plan and execute phases, the end user is going to be 
guessing IMHO.

What I think would be a clear win would be if we could get the above 
senario of multiple inserts down to one parse, one plan, n executes, and 
n binds (where binding is simply the operation of plugging values into 
the statement without having to pipe the values through the parser). 
This would be a win in most if not all circumstances where the same 
statement is executed many times.

I think it would also be nice if the new explain anaylze showed times 
for the parsing and planning stages in addition to the execution stage 
which it currently shows so there is more information for the end user 
on what approach they should take.

thanks,
--Barry

> On the other hand, there are already a few reasons to make some
> changes to the FE/BE protocol (NOTIFY messages, transaction state,
> and now possibly PREPARE/EXECUTE -- anything else?). IMHO, each of
> these isn't worth changing the protocol by itself, but perhaps if
> we can get all 3 in one swell foop it might be a good idea...
> 
> Cheers,
> 
> Neil
>