Re: Roadmap for FE/BE protocol redesign

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Barry Lind <blind(at)xythos(dot)com> writes:
>
> > 4) Protocol level support of PREPARE. In jdbc and most other
> > interfaces, there is support for parameterized SQL. If you want to take
> > advantage of the performance benefits of reusing parsed plans you have
> > to use the PREPARE SQL statement.
>
> This argument seems self-contradictory to me. There is no such benefit
> unless you're going to re-use the statement many times. Nor do I see
> how pushing PREPARE down to the protocol level will create any
> improvement in its performance.

"you're going to re-use the statement many times" is true (or should be true)
for every statement in every web site and other OLTP system. Even if the query
appears on only a single web page and is executed only once on that web page,
the nature of high volume web sites is that that page will be executed
hundreds or thousands of times per minute.

This is why the Perl DBI, for example, has a prepare_cached() which provides a
automatic caching of prepared handles. With Oracle I was able to use this
exclusively on a large high volume web site to keep thousands of prepared
handles. Every query was prepared only once per apache process.

There is a performance benefit to using placeholders and prepared queries in
that the plan doesn't need to be regenerated repeatedly. Ideally every query
should either be a big DSS query where the time spent in the optimizer is
irrelevant, or an OLTP transaction using placeholders where again the time
spent in the optimizer is irrelevant because it only needs to be run once.

This would allow the optimizer to grow in complexity. For example it could
explore both sides of the decision tree in places where now we have heuristics
to pick the probable better plan. Postgres's optimizer is pretty impressive
currently, but the constant attention to avoiding high cost optimizations
limits it.

There is also a security benefit. The idea of mixing parameters into the
queries even at the driver level gives me the willies. The database then has
to parse them back out of the query string. If there's a bug in the driver or
any kind of mismatch between the backend parser and the driver quoting then
there could be security holes.

--
greg