On Sep 4, 10:54 pm, t...@xxxxxxxxxxxxx (Tom Lane) wrote: > "Phoenix Kiula" <phoenix.ki...@xxxxxxxxx> writes: > > Would appreciate any help. Why do indexed queries take so much time? > > It's a simple DB with "10 relations" including tables and indexes. > > Simple inserts and updates, about 5000 a day, but non-trivial > > concurrent selects (about 45 million a day). Works fine when I > > restart, but a day later all goes cattywumpus. > > BTW, just to be perfectly clear: all you do is stop and restart the > postmaster (using what commands exactly?), and everything is fast again? > That's sufficiently unheard-of that I want to be entirely sure we > understood you correctly. Yes, I noticed starting the postgres database again had an effect of speed. But this does not seem to be working anymore so I suppose something else needs fixing. When I do a "select * from pg_locks", some of them show up as "Exclusive Lock". This I suppose means that the whole table is locked, right? How can I find from the "transaction id" which precise SQL statement is taking this time? I do not have anything that should! Simple SELECT, INSERT and UPDATE stuff in our fairly straightforward application, and I hope that autovacuum and auto-analyze do not take up this exclusive locks? Ref: output of the select from pg_locks -- =# select * from pg_locks; -[ RECORD 1 ]-+---------------- locktype | transactionid database | relation | page | tuple | transactionid | 47999900 classid | objid | objsubid | transaction | 47999900 pid | 21989 mode | ExclusiveLock granted | t -[ RECORD 2 ]-+---------------- locktype | relation database | 41249 relation | 10328 page | tuple | transactionid | classid | objid | objsubid | transaction | 47999900 pid | 21989 mode | AccessShareLock granted | t ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
