Re: PostgreSQL 8.4.8 bringing my website down every evening

Dne 20.6.2011 18:47, Alexander Farber napsal(a):
> isn't having prepared statements good for overall performance?

I've already mentioned that in my previous post, but let's make this
clear. Prepared statements are good for performance, but only if you're
going to execute the statement multiple times in the same session.

When running a SQL statement, the database has to parse it and plan it
first, which may be a lot of work (depending on how complex the
statement is etc.). Prepared statements allow you to do this (parsing
and planning) only once, which may significantly improve the performance.

Let's say you have a statement that takes 10ms to parse/plan and 50ms to
actually execute, and you want to execute it 100x.

If you're going to do this without prepared statements, then you'll
spend 100x 10ms for planning and 100x 50ms for execution. That's 6
seconds in total.

With prepared statements, this takes only 5 seconds. Yes, it all depends
on how much time you spend in planning vs. executing the query.

And there's a downside too - with prepared statements the the planner
can't use the actual parameter values to choose the plan (it does not
know them), so it may choose a plan that's good on average but sucks for
some parameter values.

If my assumption that your code executes each SQL exactly once per
session is right, then get right of the prepared statements and use
plain PDO::query instead. That should fix the problems you currently
have - you can keep the transaction mode in pgpool, you won't get those
annoying prepared statement exceptions and you don't need to put the
transactions there. Plus it's very likely the optimizer will be able to
come up with a better plan.

Tomas