# Postgres Indexing - A collection of indexing tips

DevFeed: [Postgres Indexing - A collection of indexing tips](<https://devfeed.tech/articles/postgres-indexing-a-collection-of-indexing-tips-41141.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2013/05/30/Postgres-Indexing-A-collection-of-indexing-tips/>)

Author: Map

Published: 2013-05-30T20:55:56Z

Content type: tutorial

Language: en

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

Topics: [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [Heroku Postgres](<https://devfeed.tech/topics/heroku-postgres.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [heroku](<https://devfeed.tech/tags/heroku.md>), [heroku-postgres](<https://devfeed.tech/tags/heroku-postgres.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [indexing](<https://devfeed.tech/tags/indexing.md>), [information-schema](<https://devfeed.tech/tags/information-schema.md>), [join](<https://devfeed.tech/tags/join.md>), [performance](<https://devfeed.tech/tags/performance.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [postgres-performance](<https://devfeed.tech/tags/postgres-performance.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [query](<https://devfeed.tech/tags/query.md>), [schema](<https://devfeed.tech/tags/schema.md>), [sql](<https://devfeed.tech/tags/sql.md>), [stat](<https://devfeed.tech/tags/stat.md>), [tips](<https://devfeed.tech/tags/tips.md>)

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