In response to Andy Chambers <achambers@xxxxxxxx>: > > 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
