debugging and monitoring database usage on postgresql

this is a query that tells you the running queries on a database, the time they have been running, the process id (pid; in case you want to kill it).... it uses the table pg_stat_activity

UPDATE FOR POSTGRES 9.4: some fields have changed name : / procpid is now pid, and current_query is now query... if it still throughs error check the fields with \d
pg_stat_activity

here it is:

select pid, now() - query_start, trim(current_query), datname, usename, waiting from pg_stat_activity where query not ilike '%IDLE%' order by query_start;

this was told to me by a friend (thanks José Zap), i don't know where did he get it from...


if you see a query having trouble, use EXPLAIN <query> to check and see what is postgresql actually doing, try to avoid SEQuential SCANS as these are much slower... create indexes as necessary... watch out don't overcreate them ; )