Ok, that was the first thing I've done, checking out the explain of the query. I don't really need the analyze part, as the plan is going for the index, which is the right decision. The updates are simple one-row updates of one column, qualified by the primary key condition. This part is OK, the query is not taking extremely long, but the problem is that we execute 500 in a transaction, and that takes too long and blocks other activities. Actually I've done an iostat run in the meantime (just learned how to use it), and looks like the disk is 100 saturated. So it clearly is a disk issue in this case. And it turns out the Oracle hardware has an edge of 3 years over what I have for postgres, so that might very well explain the performance difference... Oh well.
Next we'll upgrade the postgres hardware, and then I'll come back to report if it's working better... sorry for the noise for now. Cheers, Csaba. BTW, is the config file good enough for the kind of machine I have ? Cause it's the first time I had to make a production configuration and most of the stuff is according to the commented config guid from varlena with some guesswork added... > I'm not the most experience person on this list, but I've got some big > tables I work with. Doing an update on these big tables often involves > a sequential scan which can be quite slow. > > I would suggest posting the explain analyze output for one of your > slow updates. I'll bet it is much more revealing and takes out a lot > of the guesswork. > > -- > Matthew Nuzum > www.bearfruit.org ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster