Re: 7.3 schedule - Mailing list pgsql-hackers

On Thu, 11 Apr 2002 11:38:33 -0700
"Barry Lind" <barry@xythos.com> wrote:
> 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.

Right -- I would agree that such functionality would be nice to have.
What I meant was "is there a need for this in order to implement
PREPARE/EXECUTE"? IMHO, no -- the two features are largely
orthogonal.

> 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).

If you're inserting 100KB of data, I'd expect the time to insert
that into tables, update relevent indexes, etc. to be larger than
the time to parse the query (i.e. execution > parsing). But I
may well be wrong, I haven't done any benchmarks.
> Executing a sql statement today is the following:
> insert into table values (<stuff>);
> which does one parse, one plan, one execute

You're assuming that the cost of the "parse" step for the EXECUTE
statement is the same as "parse" for the original query, which
will often not be the case (parsing the EXECUTE statement will
be cheaper).

> 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.

I would suggest using it any time you're executing the same query
plan a large number of times. In my experience, this is very common.
There are already hooks for this in many client interfaces: e.g.
PrepareableStatement in JDBC and $dbh->prepare() in Perl DBI.

> 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

This behavior would be better, but I think the current solution is
still a "clear win", and good enough for now. I'd prefer that we
worry about implementing PREPARE/EXECUTE for now, and deal with
query binding/BLOB parser-shortcuts later -- perhaps with an FE/BE
protocol in 7.4 as Tom suggested.

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC