Re: Real time query analyzer

Adrian Suciu <adrian(dot)suciu(at)netopia-system(dot)com> schrieb:

> Hi everybody!
> I ask you for your help on a problem I have.
> I have a postgresql 7.4 running on a dual 4 GB RAM server, but I have some
> VERY memory intense queries, that put processor up to 40%. I see all this
> info in unix "top" command or ps -aux
> Unfortunately they don't show me the query itsself.
>
> Is there a tool for postgres that can show me the queries and the time they
> take and the amount of processor is consuming (as Oracle has) ?

You can do some things:

- set log_min_duration_statement = X to log all statments with a
duration more than X ms. I'm not sure, if this possible with 7.4
- select * from pg_stat_activity;
you should enable stats_command_string ;-)

Now you can identify your expensive querys and anaylse this with
EXPLAIN.

17:53 < akretschmer> ??explain
17:53 < rtfm_please> For information about explain
17:53 < rtfm_please> see http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi
17:53 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-explain.html

Read the links above to learn more about EXPLAIN.

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°