On Wed, Mar 6, 2019 at 11:49 PM Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote: > > Hi, > > On Wed, Mar 06, 2019 at 04:09:46PM -0400, Kenia Vergara wrote: > > Good afternoon. > > I need to know the commands to display the execution time, CPU usage and > > memory usage, but through the Postgres console.Thank you. > > Does this do what you want ? Note that the "QUERY STATISTICS" are log output, > and not a part of the sql result. > > $ psql postgres -Atc "SET client_min_messages=log; SET log_statement_stats=on" -c 'explain analyze SELECT max(i) FROM generate_series(1,999999)i' > SET > LOG: QUERY STATISTICS > DETAIL: ! system usage stats: > ! 0.625402 s user, 0.059799 s system, 0.687496 s elapsed > ! [0.629882 s user, 0.059799 s system total] > ! 10672 kB max resident size > ! 0/27344 [0/27344] filesystem blocks in/out > ! 0/1378 [0/2365] page faults/reclaims, 0 [0] swaps > ! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent > ! 0/167 [2/167] voluntary/involuntary context switches > Aggregate (cost=12.50..12.51 rows=1 width=4) (actual time=680.155..680.161 rows=1 loops=1) > -> Function Scan on generate_series i (cost=0.00..10.00 rows=1000 width=4) (actual time=253.512..497.462 rows=999999 loops=1) > Planning Time: 0.227 ms > Execution Time: 686.200 ms You could also consider using pg_stat_kcache (https://github.com/powa-team/pg_stat_kcache/) extension, which gather most of those metrics and accumulate them per (queryid, dbid, userid). It requires more configuration, depends on pg_stat_statements extension, and need a postgres restart to activate it though.
