Analysing/Profiling queries on PostgreSQL

sql postgresql plpgsql postgresql-8.4
Andy.l picture Andy.l · Mar 5, 2015 · Viewed 8.2k times · Source

I've just inherited an old PostgreSQL installation and need to do some diagnostics to find out why this database is running slow. On MS SQL you would use a tool such as Profiler to see what queries are running and then see how their execution plan looks like.

What tools, if any, exist for PostgreSQL that I can do this with? I would appreciate any help since I´m quite new with Postgres.

Answer

Vao Tsun picture Vao Tsun · Mar 11, 2015

Use pg_stat_statements extension to get long running queries. then use select* from pg_stat_statements order by total_time/calls desc limit 10 to get ten longest. then use explain to see the plan...

Related questions

Incrementing a number in a loop in plpgsql

I couldn't find this immediately from the examples. I want to increment a variable in a loop, in a function. For instance: DECLARE iterator float4; BEGIN iterator = 1; while iterator < 999 ..... iterator ++; END; How would this be done? I was looking …

Read More
PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Read More
How to return result of a SELECT inside a function in PostgreSQL?

I have this function in PostgreSQL, but I don't know how to return the result of the query: CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER) RETURNS SETOF RECORD AS $$ BEGIN SELECT text, count(*), 100 / maxTokens * count(*) FROM ( SELECT text FROM token …

Read More