PostgreSQL for SQL Server DBAs – What is an alternative to sys.dm_exec_query_stats in the PostgreSQL world?

sys.dm_exec_query_stats is a Dynamic Management View (DMV) in Microsoft SQL Server that provides performance statistics for cached query plans in SQL Server. It’s used for monitoring and identifying performance issues with SQL queries. This DMV can be very useful for database administrators and developers to analyze the performance of SQL queries, understand how often they are executed, and identify which queries are consuming the most resources.

Here is a basic example of how you might use sys.dm_exec_query_stats to get information about query execution times, CPU time, logical reads, and so on:

This query returns statistics about the executed SQL statements, including the number of times each statement was executed (execution_count), total logical reads and writes, total worker (CPU) time, total elapsed time, average elapsed time per execution, and the text of the SQL statement itself.

In PostgreSQL, there isn’t a direct equivalent to SQL Server’s sys.dm_exec_query_stats DMV, but you can get similar insights using a combination of PostgreSQL’s system catalogs and views, particularly the pg_stat_statementsextension. This extension provides a means to track execution statistics of all SQL statements executed by a server.

First, ensure that the pg_stat_statements module is enabled in your PostgreSQL instance. This can usually be done by adding pg_stat_statements to the shared_preload_libraries in your PostgreSQL configuration file (postgresql.conf), and then restarting the PostgreSQL server. You may also need to create the extension in your database with:

Once pg_stat_statements is enabled, you can query its view to get query performance statistics. Here’s an example query similar in spirit to the SQL Server example:

This will give you:

  • query: Text of a representative query, with some values anonymized.
  • calls: Number of times the statement was executed.
  • total_time: Total time spent in the statement, in milliseconds.
  • rows: Total number of rows retrieved or affected by the statement.
  • min_time, max_time, mean_time, stddev_time: Minimum, maximum, mean, and standard deviation of the execution times for the statement, respectively.
  • blocks_hit: Number of times disk blocks were found already in the buffer cache, avoiding disk reads.
  • blocks_read: Number of disk blocks read.

This view is extremely useful for identifying slow queries, frequently executed queries, and queries that are reading a lot of data from disk.

Keep in mind that pg_stat_statements tracks queries across all databases in the server by default, and its data persists across server restarts until it’s explicitly reset using functions like pg_stat_reset() or pg_stat_statements_reset(). Permissions to access pg_stat_statements data can be managed at the PostgreSQL role level.

About Shiv Iyer 455 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.