# pg\_stat\_statements Published articles for pg\_stat\_statements. This is one page of public article previews, not the complete archive. Follow Next page to continue. Summaries are not the original full articles. ## Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL DevFeed: [Postgres Tips: CTEs, .psqlrc Configuration, Query Statistics, and ETL]() Original publisher: [Read original article]() Author: Map Published: 2015-12-29T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [SQL](), [etl]() Tags: [cte](), [pg-stat-statements](), [postgres](), [postgresql](), [setup](), [sql](), [tips]() ### AI overview A practical collection of Postgres tips covering Common Table Expressions for readable queries, .psqlrc customization, pg_stat_statements for identifying indexing and performance opportunities, and caution around aggregating data through ETL when working across microservices or applications. ### Source excerpt I find during the holiday season many pick up new books, learn a new language, or brush up on some other skill in general. Here's my contribution to hopefully giving you a few new things to learn about Postgres and ideally utilize in the new year. It's not in a top 10 list as much as 10 tips and tricks you should be aware of as when you need them they become incredibly handy. But, first a shameless plug if you find any of the following helpful, consider subscribing to Postgres weekly a weekly newsletter with interesting Postgres content. 1. CTEs - Common Table Expressions CTEs allow you to do crazy awesome things like recursive queries but even the most simple form of them I don't go a day without using. Think of a CTE or commonly known as with clause as a view inside the time that query is running. This lets you more easily create readable query. Any query that's constructed that's even 100 lines long, but with 4-5 CTEs is undoubtedly going to be easier for someone new to come in and understand than a 20 line query that does the same thing. A few people like writing SQL, but no one likes reading someone else's so do them a favor and read up on CTEs. 2. Setup a .psqlrc You setup a bashrc, vimrc, etc. Why not do the same for Postgres. Some of the great things you can do: Setup pretty formatting by default with \x auto Set nulls to actually look like something \pset null ¤ Turn timing on by default \timing on Customize your prompt \set PROMPT1 '%[%033[33;1m%]%x%[%033[0m%]%[%033[1m%]%/%[%033[0m%]%R%# ' Save commonly run queries that you can run by name Here's an example of my own psqlrc: \set QUIET 1 \pset null '¤' -- Customize prompts \set PROMPT1 '%[%033[1m%][%/] # ' \set PROMPT2 '... # ' -- Show how long each query takes to execute \timing -- Use best available output format \x auto \set VERBOSITY verbose \set HISTFILE ~/.psql_history- :DBNAME \set HISTCONTROL ignoredups \set COMP_KEYWORD_CASE upper \unset QUIET 3. pg_stat_statements for where to index pg_stat_state ## Examining Postgres 9.4 - A first look DevFeed: [Examining Postgres 9.4 - A first look]() Original publisher: [Read original article]() Author: Map Published: 2014-02-02T20:55:56Z Content type: article Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Caching](), [Databases](), [functions]() Tags: [aggregate-functions](), [cache](), [failover](), [pg-stat-statements](), [postgres](), [postgresql](), [read-replica]() ### AI overview A first look at PostgreSQL 9.4 while it is still in its final commit fest. The article discusses pg_prewarm for loading data into memory, concurrent materialized-view refreshes, and ordered-set aggregates, while noting that additional features may still change before release. ### Source excerpt PostgreSQL is currently entering its final commit fest. While its still going, which means there could still be more great features to come, we can start to take a look at what you can expect from it now. This release seems to bring a lot of minor increments versus some bigger highlights of previous ones. At the same time there's still a lot on the bubble that may or may not make it which could entirely change the shape of this one. For a peek back of some of the past ones: Highlights of 9.2 pg_stat_statements Index only scans JSON Support Range types Huge performance improvements Highlights of 9.3 Postgres foreign data wrapper Materialized views Checksums On to 9.4 With 9.4 instead of a simply list lets dive into a little deeper to the more noticable one. pg_prewarm I'll lead with one that those who need it should see huge gains (read larger apps that have a read replica they eventually may fail over to). Pg_prewarm will pre-warm your cache by loading data into memory. You may be interested in running pg_prewarm before bringing up a new Postgres DB or on a replica to keep it fresh. Why it matters - If you have a read replica it won't have the same cache as the leader. This can work great as you can send queries to it and it'll optimize its own cache. However, if you're using it as a failover when you do have to failover you'll be running in a degraded mode while your cache warms up. Running pg_pregwarm against it on a periodic basis will make the experience when you do failover a much better one. Refresh materialized view concurrently Materialized views just came into Postgres in 9.3. The problem with them is they were largely unusable. This was because they 1. Didn't auto-refresh and 2. When you did refresh them it would lock the table while it ran the refresh making it unreadable during that time. Materialized views are often most helpful on large reporting tables that can take some time to generate. Often such a query can take 10-30 minutes or even more to run. ## More on Postgres Performance DevFeed: [More on Postgres Performance]() Original publisher: [Read original article]() Author: Map Published: 2013-01-10T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Database](), [data]() Tags: [development](), [indexes](), [optimizing](), [performance](), [pg-stat-statements](), [postgres](), [postgres-performance](), [query]() ### AI overview A practical guide to PostgreSQL performance optimization using pg_stat_statements to identify high-cost queries, analyze total and average execution time, and investigate indexes and query plans. ### Source excerpt If you missed my previous post on Understanding Postgres Performance its a great starting point. On this particular post I'm going to dig in to some real life examples of optimizing queries and indexes. It all starts with stats I wrote about some of the great new features in Postgres 9.2 in the recent announcement on support of Postgres 9.2 on Heroku. One of those awesome features, is pg_stat_statements. Its not commonly known how much information Postgres keeps about your database (beyond the data of course), but in reality it keeps a great deal. Ranging from basic stuff like table size to cardinality of joins to distribution of indexes, and with pg_stat_statments it keeps a normalized record of when queries are run. First you'll want to turn on pg_stat_statments: CREATE extension pg_stat_statements; What this means it would record both: SELECT id FROM users WHERE email LIKE 'craig@heroku.com'; and SELECT id FROM users WHERE email LIKE 'craig.kerstiens@gmail.com'; To a normalized form which looks like this: SELECT id FROM users WHERE email LIKE ?; Understanding them from afar While Postgres collects a great deal of this information dissecting it to something useful is sometimes more mystery than it should be. This simple query will show a few very key pieces of information that allow you to begin optimizing: SELECT (total_time / 1000 / 60) as total_minutes, (total_time/calls) as average_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 100; The above query shows three key things: The total time a query has occupied against your system in minutes The average time it takes to run in milliseconds The query itself Giving an output something like: total_time | avg_time | query ------------------+------------------+------------------------------------------------------------ 295.761165833319 | 10.1374053278061 | SELECT id FROM users WHERE email LIKE ? 219.138564283326 | 80.24530822355305 | SELECT * FROM address WHERE user_id = ? AND current = True (2 rows) What t ## Finding plans in pg\_stat\_plans easily with pg\_find\_plans DevFeed: [Finding plans in pg\_stat\_plans easily with pg\_find\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-12-04T10:40:00Z Content type: article Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [JSON](), [Python](), [Monitoring]() Tags: [join](), [json](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [python](), [sql]() ### AI overview The article introduces pg_find_plans, an experimental pg_stat_plans submodule for PostgreSQL. It uses machine-readable EXPLAIN output to search stored execution plans by criteria such as sequential scans, execution costs, and join counts directly from SQL. The article also discusses its PL/Python and PL/pgSQL implementation, input sanitization, limitations, and the usefulness of JSON for representing plan structures. ### Source excerpt As I recently blogged about, pg_stat_plans is a PostgreSQL satellite project I've been working on that aims to support earlier versions of Postgres that cannot use the new pg_stat_statements, and to track execution costs at the plan rather than the query granularity. It allows the user to easily explain each stored query text to see the plan for the entry, and has features that facilitate monitoring planner regressions. Since PostgreSQL 9.0, support for machine-readable EXPLAIN output has existed. I'm not aware that anyone else got around to actually doing something interesting with this capability, though. I knew that in order to get the most benefit from pg_stat_plans, it ought to be possible to leverage this capability to search for plans based on arbitrary criteria, directly from SQL. I've written an experimental submodule of pg_stat_plans, called pg_find_plans, that is designed to do just that - to quickly find plans and their execution costs, for those plans that, say, perform a sequential scan on a known large table. Here's the description of pg_find_plans from its documentation: pg_find_plans is written in PL/Python and PL/PgSQL. It is intended to provide users with a better way to ask questions like "what are the execution costs of all plans tracked since last statistics reset that involve a sequential scan against mytable, and have more than 2 joins?". That might be written as: mydb=# select join_count(json_plan), p.* from pg_stat_plans p join stored_plans sp on (p.userid=sp.userid and p.dbid=sp.dbid and p.planid=sp.planid) where from_our_database and join_count(json_plan) > 2 and contains_node(json_plan, 'Seq Scan', 'mytable'); order by 1 desc nulls last; Users should have a high degree of confidence that their queries on plan's structure are free of detectable errors, and pg_find_plans ensures this by carefully sanitising user input. For example, if the node of interest was specified as 'seq scan' above, the query would raise an error - to do any less mi ## First release of pg\_stat\_plans DevFeed: [First release of pg\_stat\_plans]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-10-20T14:53:00Z Content type: release Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [SQL](), [Open Source](), [Logging]() Tags: [database](), [open-source](), [performance](), [pg-stat-plans](), [pg-stat-statements](), [planner](), [postgresql](), [query](), [release](), [sql]() ### AI overview The article announces the first release of pg_stat_plans, an open-source PostgreSQL module based on pg_stat_statements. It analyzes plan execution costs and planner regressions, extends normalized-statement analysis to PostgreSQL 9.0 and 9.1, and tracks plan costs over time. ### Source excerpt Anyone who attended my recent talk at Postgres Open, which was co-presented with my 2ndQuadrant colleague Greg Smith, "Beyond Query Logging", will be aware that pg_stat_statements, the standard contrib module that assigns execution costs to queries and makes them available from a view in the database, has been improved considerably in the recent 9.2 Postgres release. It has been improved in a way that we believe will alter the preferred approach to workload analysis on PostgreSQL databases away from log analysis tools, which just don't offer the performance, flexibility or granularity of this new approach. We also announced a new open source tool that addresses a related but slightly different problem (the analysis of plan execution costs, and planner regressions), as well as making most of the benefits of pg_stat_statements on 9.2 available to users stuck on earlier versions of Postgres. This new tool is called pg_stat_plans, and is itself based on pg_stat_statements. The 9.2 pg_stat_statements feature of particular importance, the ability to "normalise" non-prepared statements that the large majority of applications use exclusively is now brought to earlier versions (versions 9.0 and 9.1, though pg_stat_plans works fine on 9.2 too). Since pg_stat_plans fingerprints plans rather than query trees, the way this works is slightly different to pg_stat_statements, and perhaps doesn't quite match people's intuitive expectations about how normalisation ought to behave in some cases. These differences have been extensively documented. pg_stat_plans also has the ability to EXPLAIN a stored, representative SQL text, in order to facilitate deeper analysis of plan execution costs. Plan total_cost and startup_cost is tracked over time for each plan, for example, so that the "crossover point" at which the planner begins to prefer an alternative plan can sometimes be observed, and the planner's "reasoning" can perhaps be better understood. pg_stat_plans is distributed under the P ## Much improved statement statistics coming to Postgres 9.2 DevFeed: [Much improved statement statistics coming to Postgres 9.2]() Original publisher: [Read original article]() Author: Peter Geoghegan (noreply@blogger.com) Published: 2012-03-29T13:02:00Z Content type: opinion Language: en Sources: [Peter Geoghegan's blog]() Topics: [PostgreSQL](), [Databases](), [Query (disambiguation)](), [Parsing]() Tags: [c](), [database](), [optimisation](), [performance](), [pg-stat-statements](), [postgres](), [postgresql](), [query]() ### AI overview This article discusses an improvement to PostgreSQL's pg_stat_statements module for Postgres 9.2. The change normalizes similar queries by fingerprinting analyzed parse-tree fields, allowing statistics from queries that differ only in constants to be grouped together for production performance analysis. ### Source excerpt There is a tendency for people with an interest in improving databases performance to imagine that it mostly boils down to factors outside of their application - the hardware, operating system configuration, and database settings. While these are obviously crucially important, experience suggests that in most cases, by far the largest gains are to be had by optimising the application's interaction with the database. Doing so invariably involves analysing what queries are being executed in production, their costs, and what the significance of the query is to the application or business process that the database supports. PostgreSQL has had a module available in contrib since version 8.4 - pg_stat_statements, originally developed by Takahiro Itagaki. The module blames execution costs on queries, so that bottlenecks in production can be isolated to points in the application. It does so by providing a view that is continually updated, giving real-time statistical information. Here is an example from the Postgres 9.2 docs: bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--------------------------------------------------------------------- query | UPDATE pgbench_branches SET bbalance = bbalance + ? WHERE bid = ?; calls | 3000 total_time | 9.60900100000002 rows | 2836 hit_percent | 99.9778970000200936 -[ RECORD 2 ]--------------------------------------------------------------------- query | UPDATE pgbench_tellers SET tbalance = tbalance + ? WHERE tid = ?; calls | 3000 total_time | 8.015156 rows | 2990 hit_percent | 99.9731126579631345 -[ RECORD 3 ]--------------------------------------------------------------------- query | copy pgbench_accounts from stdin calls | 1 total_time | 0.310624 rows | 100000 hit_percent | 0.303