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