Re: PostgreSQL 8.4.8 bringing my website down every evening

From: Tomas Vondra <tv(at)fuzzy(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL 8.4.8 bringing my website down every evening
Date: 2011-06-24 23:51:48
Message-ID: 4E052314.6000002@fuzzy.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2011-06-25 00:15:30 Re: PostgreSQL 8.4.8 bringing my website down every evening
Previous Message Guillaume Lelarge 2011-06-24 17:47:09 Re: pg_dumpall custom format?