it's my update -
UPDATE "users" SET "name" = $?, "is_read" = $?, "updated_at" = $? WHERE "users"."id" = ?
and sometimes it takes about 150 seconds(but in normaly - it's fast). Could you please advise me possible reasons? users.id have index
Asked
Active
Viewed 163 times
0
Artoym P
- 53
- 1
- 5
-
Any triggers? Is users.id indexed? – jarlh Nov 15 '15 at 18:23
-
yes, users.id indexed. if I have view with this table - it can be reason? – Artoym P Nov 15 '15 at 18:32
-
Views shouldn't make any difference. Index re-build can perhaps make some difference, but I don't know postgresql that well... Triggers can make huge difference. – jarlh Nov 15 '15 at 18:35
-
This might help (HOT updates) http://stackoverflow.com/a/3100232/3574819 – FuzzyTree Nov 15 '15 at 18:37
-
Look at `select * from pg_stat_activity`. Maybe some query lock the table? – Tomasz Jakub Rup Nov 15 '15 at 18:57
1 Answers
0
To speed this update, you want an index on users(id):
create index idx_users_id on users(id)
Normally, the id would be declared as a primary key (or at least unique) and already have an index. But if it isn't, this should help.
Note: There are other possibilities, such as other processes having a lock on the table or very complex insert triggers.
Gordon Linoff
- 1,242,037
- 58
- 646
- 786
-
Thank you, index on users(id) exist. What about views with users table? it can be reason? – Artoym P Nov 15 '15 at 18:31
-
@ArtoymP . . . Views can also have a negative impact on performance. – Gordon Linoff Nov 15 '15 at 23:27