How to log query execution time in pg_log

Products

• VMware Tanzu Greenplum

Issue/Introduction

This articles covers how to log the query execution time in pg_log. You may find it necessary to focus on the query execution time when doing query analysis with logs.

Environment

All GP versions as of now.

Resolution

Set the GUC log_statement_stats to on and then reload the configuration with gpstop -u.

When you run a query, you see the following in pg_log:

2021-08-04 02:20:04.788907 CST,"gpadmin","gpadmin",p12522,th-184600448,"[local]",,2021-08-04 02:16:39 CST,0,con35,cmd5,seg-1,,,,,"LOG","00000","QUERY STATISTICS","! system usage stats:
!       0.144050 elapsed 0.002235 user 0.001081 system sec
!       [0.024000 user 0.016800 sys total]
!       0/0 [0/1072] filesystem blocks in/out
!       0/388 [0/6870] page faults/reclaims, 0 [0] swaps
!       0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
!       13/0 [888/0] voluntary/involuntary context switches",,,,,"select * from t1;",0,,"postgres.c",5846,

In this example, 0.144050 is the execution time of the query and the unit is in seconds.

For more information regarding this GUC, please refer following doc link:

GP6:
https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-config_params-guc-list.html#log_statement_stats

GP7:
https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/ref_guide-config_params-guc-list.html#log_statement_stats