# More on Postgres Performance

DevFeed: [More on Postgres Performance](<https://devfeed.tech/articles/more-on-postgres-performance-41124.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/01/10/More-on-Postgres-Performance/>)

Author: Map

Published: 2013-01-10T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [Database](<https://devfeed.tech/topics/database.md>), [data](<https://devfeed.tech/topics/data.md>)

Tags: [development](<https://devfeed.tech/tags/development.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [optimizing](<https://devfeed.tech/tags/optimizing.md>), [performance](<https://devfeed.tech/tags/performance.md>), [pg-stat-statements](<https://devfeed.tech/tags/pg-stat-statements.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgres-performance](<https://devfeed.tech/tags/postgres-performance.md>), [query](<https://devfeed.tech/tags/query.md>)

## 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