launchpad-dev team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

On Tue, Sep 21, 2010 at 5:00 AM, Ian Booth <ian.booth@xxxxxxxxxxxxx> wrote:
> Hi
>
> In keeping with the theme of Performance Tuesday, I thought I'd ask a
> question about a particular implementation aspect since it potentially
> relates to performance.
>
> My question is: we don't appear to be using SQL prepared statements when
> accessing the database; why is this? Given the amount of SQL being
> executed, there's potentially a significant performance gain to be had,
> not to mention the reduced potential for SQL injection type attacks on
> the system. Having come from an Oracle background, using prepared
> statements is a no brainer for me. Perhaps there's a Python/Postgres
> issue I'm not aware of?
>
> Thanks for any insight.

Few people use prepared statements with PostgreSQL as they are rarely
a win. With a prepared statement, a query plan must be chosen without
paying attention to the arguments and this will often perform much
worse than a query plan that is chosen when the arguments are known -
PostgreSQL looks at the statistical analysis of the data being queried
and chooses what it believes to be the most efficient query plan. For
example, PostgreSQL knows that well over half of our bugs are targeted
to ubuntu but few bugs are targeted to mandriva so you get totally
different query plans. Also in the modern world with modern CPUs the
time spent in the planner is usually insignificant.


-- 
Stuart Bishop <stuart@xxxxxxxxxxxxxxxx>
http://www.stuartbishop.net/