Regras curtas. (Alguns deles são criados automaticamente, mas podem ser descartados manualmente mais tarde, dependendo de seus dbms. Não assuma que você sempre trabalhará no PostgreSQL.)

• Indexe cada chave primária.
• Indexe cada chave estrangeira.
• Indexe cada coluna usada em uma cláusula JOIN.
• Indexe cada coluna usada em uma cláusula WHERE.
• Estude sua documentação para aprender as opções de indexação "esotéricas" que seu dbms suporta.

Cada chave primária significa que as chaves primárias de várias colunas devem ter um único índice cobrindo todas as colunas. O PostgreSQL criará esse índice automaticamente se você declarar uma chave primária de várias colunas.

Há muitos casos em que um único índice de várias colunas oferece melhor desempenho do que vários índices de coluna única. Monitore consultas lentas e faça testes para descobrir qual é qual.

Suponha que qualquer alteração na indexação melhorará algumas atividades do banco de dados e degradará outras. Acho útil ter um conjunto de instruções SQL que eu possa criar perfil antes e depois de fazer alterações nos índices. Este conjunto inclui instruções SELECT, INSERT, UPDATE e DELETE.

Não há substituto para estudar os documentos para seus dbms específicos.

• CRIAR ÍNDICE
• Índices (Observe especialmente as seções sobre expressões de indexação, sobre índices parciais e sobre como examinar o uso do índice)

Além do que o @Catcall já forneceu , e para adicionar um pequeno corretivo.

As respostas até agora parecem indicar que você precisaria criar índices em chaves primárias, mas esse não é o caso do PostgreSQL (exceções parciais se aplicam). O manual:

O PostgreSQL cria automaticamente um índice exclusivo quando uma restrição exclusiva ou chave primária é definida para uma tabela. O índice abrange as colunas que compõem a chave primária ou restrição exclusiva (um índice de várias colunas, se apropriado) e é o mecanismo que impõe a restrição.

Minha ênfase em negrito.

Você pode querer criar índices adicionais para a segunda coluna ou colunas posteriores de um índice de várias colunas, mas o primeiro geralmente é coberto muito bem por um índice de várias colunas - exceto quando colunas adicionais tornam o índice muito maior. Discutimos isso em detalhes nesta questão relacionada:

• Um índice composto também é bom para consultas no primeiro campo?

Índices de várias colunas , índices parciais e índices em expressões são ferramentas particularmente poderosas no PostgreSQL. Desde o PostgreSQL 9.2 também existem scans somente de índice , preenchendo o papel de "cobrir índices" em outros RDBMS. Este não é outro tipo de índice, mas um novo recurso do RDBMS com tipos de índice existentes.

Cada índice carrega custos específicos , portanto, não há como contornar alguns conhecimentos básicos para realmente otimizar a indexação. Apenas criar mais índices pode fazer mais mal do que bem. Em particular, os índices podem impedir que as atualizações HOT melhorem o desempenho.

Geralmente, as operações de gravação ( INSERT, DELETE, UPDATE) se tornam mais caras (mas as duas últimas também podem se beneficiar!), enquanto as operações de leitura ( SELECT) geralmente se beneficiam. Muitos índices podem esgotar a memória cache, de modo que até mesmo as operações de leitura podem sofrer.

Esta página do Postgres Wiki sobre manutenção de índices apresenta ferramentas para encontrar índices duplicados ou não utilizados, entre outras coisas.

Resposta relacionada no SO:

• Otimizar tabelas somente leitura do PostgreSQL

Existem duas opções.

1. Faz você.
2. A tecnologia faz isso.

A resposta para fazer você mesmo é bastante exaustivamente documentada aqui. Então vamos ver outra coisa.

Pghero

A Pghero pode ajudá-lo se você quiser alguns conselhos automatizados.

Dito isto, tem algumas deficiências.

1. Ele só funciona em WHEREe ORDER BY, não JOINS.
2. Ele usa apenas estatísticas de porcentagem NULL e valores distintos.

Confira este vídeo para mais informações .