# Postgres Performance Published articles for Postgres Performance. 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 Indexing - A collection of indexing tips DevFeed: [Postgres Indexing - A collection of indexing tips]() Original publisher: [Read original article]() Author: Map Published: 2013-05-30T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [Databases](), [SQL](), [Heroku Postgres](), [JOIN]() Tags: [heroku](), [heroku-postgres](), [indexes](), [indexing](), [information-schema](), [join](), [performance](), [postgres](), [postgres-performance](), [postgresql](), [query](), [schema](), [sql](), [stat](), [tips]() ### AI overview A collection of PostgreSQL indexing tips covering unused-index analysis, indexing costs, and the trade-offs between composite and separate indexes. It includes SQL examples and Heroku tooling for examining index usage. ### Source excerpt Even from intial reviews of my previous post on expression based indexes I received a lot of questions and feedback around many different parts of indexing in Postgres. Here's a mixed collection of valuable tips and guides around much of that. Unused Indexes In an earlier tweet I joked about some SQL that would generate the SQL to add an index to every column: # SELECT 'CREATE INDEX idx_' || table_name || '_' || column_name || ' ON ' || table_name || ' ("' || column_name || '");' FROM information_schema.columns; ?column? --------------------------------------------------------------------- CREATE INDEX idx_pg_proc_proname ON pg_proc ("proname"); CREATE INDEX idx_pg_proc_pronamespace ON pg_proc ("pronamespace"); CREATE INDEX idx_pg_proc_proowner ON pg_proc ("proowner"); The reasoning behind this is guessing whether an index will be helpful can be a bit hard within Postgres. So the easy solution is to add indexes to everything, then just observe if they're being used. Of course you want to add it to all tables/columns because you never know if core of Postgres may be missing some needed ones As included with the pg-extras plugin for Heroku you can run a query to show you all unused indexes. On Heroku simply install the plugin the run heroku pg:unused_indexes to show the size and number of times an index scan has been used. On a non Heroku Postgres database you can run: # SELECT schemaname || '.' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST, pg_relation_size(i.indexrelid) DESC; table | index | index_size | index_scans ---------------------+--------------------------------------------+------------+------------- public.grade_levels | index_placement_attempt ## 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 ## Understanding Postgres Performance DevFeed: [Understanding Postgres Performance]() Original publisher: [Read original article]() Author: Map Published: 2012-10-01T20:55:56Z Content type: tutorial Language: en Sources: [Craig Kerstiens]() Topics: [PostgreSQL](), [Caching](), [Database](), [Heroku Postgres](), [Amazon EC2]() Tags: [cache](), [database](), [database-performance](), [ec2](), [heroku](), [heroku-postgres](), [index](), [indexes](), [postgres](), [postgres-performance](), [postgresql]() ### AI overview A practical guide to assessing PostgreSQL performance for application developers. It explains how to check cache hit rates, when to increase available database cache, and how index usage and table size can indicate opportunities for improvement. ### Source excerpt Update theres a more recent post that expands further on where to start optimizing specific queries, and of course if you want to dig into optimizing your infrastructure High Performance PostgreSQL is still a great read For many application developers their database is a black box. Data goes in, comes back out and in between there developers hope its a pretty short time span. Without becoming a DBA there's a few pieces of data that most application developers can easily grok which will help them understand if their database is performing adequately. This post will provide some quick tips that allow you to determine whether your database performance is slowing down your app, and if so what you can do about it. Understanding your Cache and its Hit Rate The typical rule for most applications is that only a fraction of its data is regularly accessed. As with many other things data can tend to follow the 80/20 rule with 20% of your data accounting for 80% of the reads and often times its higher than this. Postgres itself actually tracks access patterns of your data and will on its own keep frequently accessed data in cache. Generally you want your database to have a cache hit rate of about 99%. You can find your cache hit rate with: SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables; We can see in this dataclip that the cache rate for Heroku Postgres is 99.99%. If you find yourself with a ratio significantly lower than 99% then you likely want to consider increasing the cache available to your database, you can do this on Heroku Postgres by performing a fast database changeover or on something like EC2 by performing a dump/restore to a larger instance size. Understanding Index Usage The other primary piece for improving performance is indexes. Several frameworks will add indexes on your primary keys, though if you're searching on other fields or joini