Display Running Slow Queries Using PostgreSQL Views

Imagine trying to figure out which are your slowest, currently running queries. You would usually go and type

SELECT * FROM pg_stat_activity;

Here you would do some sorting and filtering and would eventually come up with something like this:

SELECT pg_stat_activity.pid,                                          
     (now() - pg_stat_activity.query_start) AS duration,                 
   pg_stat_activity.usename,                                           
   pg_stat_activity.query,                                             
   pg_stat_activity.state                                              
   FROM pg_stat_activity                                                
   WHERE ((now() - pg_stat_activity.query_start) > '00:05:00'::interval)
ORDER BY duration DESC;

You may not want to copy-paste this beauty every time you are curious about the slow/stuck queries, right?

Views are a great way to store a big boring query. We can take the query above and put it in a view called long_running_queries:

CREATE VIEW long_running_queries AS (
   SELECT pg_stat_activity.pid,                                          
     (now() - pg_stat_activity.query_start) AS duration,                 
   pg_stat_activity.usename,                                           
   pg_stat_activity.query,                                             
   pg_stat_activity.state                                              
   FROM pg_stat_activity                                                
   WHERE ((now() - pg_stat_activity.query_start) > '00:05:00'::interval)
   ORDER BY duration DESC
);

Query the view to get all the information about the long runners:

SELECT * FROM long_running_queries;

Please can we just go back and get the Toshiba Handibook?

Play with the view: get the top 5 long runners created by Cartman:

SELECT * FROM long_running_queries 
WHERE usename = 'cartman'
LIMIT 5;

Finally, if you are sure nothing depends on the view, get rid of it:

DROP VIEW long_running_queries;

A view definition is the query which was used to create the view. Display all the views and their view definitions:

SELECT schemaname, viewname, definition
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;

… és készen is vagyunk!

Notes

• tables and materialized views write data on the disk, views do not.
• using views will not improve performance
• storing views do not require significant disk space
• for the parser, there is no difference between a table and a view