How to find current and historical queries in Greenplum database

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

This KB article covers how to list queries currently running and queries that were completed in the past.

Resolution

Currently Running Queries

The currently running queries can be listed using the view "pg_stat_activity":

SELECT * FROM pg_stat_activity;

To list queries that are running for longer than 15 minutes:

SELECT * FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '15 minutes';

Historical Queries

Use the Greenplum Command Center WebUI to list completed queries. See "Query History" section in the "Monitoring and Managing VMware Greenplum Queries" documentation.

It is also possible to connect to the "gpperfmon" database in the cluster with "psql -d gpperfmon" and list completed queries using the "gpmetrics.gpcc_queries_history" table:

SELECT * FROM gpmetrics.gpcc_queries_history;