Re: Update takes longer than expected

In response to Andy Chambers <achambers(at)mcna(dot)net>:
>
> I have an update that takes longer than expected and wondered if
> there's an easy way to make it go faster.
>
> It's pretty simple:-
>
> create table session (
> id serial primary key,
> data text);
>
> update session
> set data = 'ipsum lorem...'
> where id = 5;
>
> The "ipsum lorem.." stuff is an encrypted session variable from a
> rails app that does tend to get quite large
>
> select avg(length(data)) from session
> => 31275
>
> We're trying to migrate the app from mysql to pg and this is one of
> the performance bottle-necks. Unfortunately it slows down every
> request by about 5 seconds. MySQL (both MyISAM and InnoDB) does this
> almost instantaneously.

Those aren't the types of queries that normally take a long time in
PostgreSQL, so my initial guess is that your DB server is very poorly
tuned. What is your vacuum strategy? for example. That fact that
you aren't mentioning any of these things leads me to guess that
you're new enough to PostgreSQL that you need to get yourself up to
speed on basid PostgreSQL config. There's a lot here, but you'll
be much more comfortable with things if you familiarize yourself with
this chapter:
http://www.postgresql.org/docs/9.1/static/runtime-config.html

On a more targeted level, doing a:
EXPLAIN ANALYZE update session
set data = 'ipsum lorem...'
where id = 5;

Will give you details on what's taking so long. If the output of
that doesn't help, you can include it in an email to the list and
people will provide details on what it means and advice on how to fix
it.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/