Re: Statistics collection question

On Sep 4, 10:54 pm, t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) wrote:
> "Phoenix Kiula" <phoenix(dot)ki(dot)(dot)(dot)(at)gmail(dot)com> 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