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
> 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.
In response to
pgsql-performance by date
|Next:||From: Alan McKay||Date: 2009-06-23 23:21:38|
|Subject: SOLVED: processor running queue - general rule of thumb?|
|Previous:||From: Alan McKay||Date: 2009-06-23 20:41:53|
|Subject: Re: processor running queue - general rule of thumb?|