Craig James a écrit : > 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: The last two columns only store TRUE or FALSE, they're booleans. So you're saying that an index on them might be useless ? We're retrieving 1000-2000 rows max and we need to extract only those who have TRUE on the last column for example. > > 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). They are 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. I'll investigate that way. > > Then to minimize the impact on your app, create a view that looks like > the original table for read-only apps. Good idea, thanks again. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
