Re: 7.3 schedule

Neil Conway wrote:
> On Thu, 11 Apr 2002 16:25:24 +1000
> "Ashley Cambrell" <ash(at)freaky-namuh(dot)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
>