Mathieu Nebra wrote:
Greg Stark a écrit :
All the other comments are accurate, though it does seem like
something the database ought to be able to handle.
The other thing which hasn't been mentioned is that you have a lot of
indexes. Updates require maintaining all those indexes. Are all of
these indexes really necessary? Do you have routine queries which look
up users based on their flags? Or all all your oltp transactions for
specific userids in which case you probably just need the index on
userid.
We are using these indexes, but I can't be sure if we _really_ need them
or not.
I can go into detail. We have:
UserID - TopicID - LastReadAnswerID - WrittenStatus - IsFavorite
So basically, we toggle the boolean flag WrittenStatus when the user has
written in that topic. The same goes for IsFavorite.
Do those last two columns hold much data? Another thing to consider is to split this into two tables:
UserID - TopicID - LastReadAnswerID
UserID - TopicID - WrittenStatus - IsFavorite
As others have pointed out, an UPDATE in Postgres is a select/delete/insert, and if you're updating just the LastReadAnswerID all the time, you're wasting time deleting and re-inserting a lot of data that never change (assuming they're not trivially small columns).
This might also solve the problem of too many indexes -- the table that's updated frequently would only have an index on (UserID, TopicID), so the update only affects one index.
Then to minimize the impact on your app, create a view that looks like the original table for read-only apps.
Craig
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
