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
LastReadAnswerIDall the time, you're wasting time deleting and re-inserting a lot of data that never change (assuming
they'renot 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