O título é grande mas eu vou tentar explicar da forma mais rápida possível. Na verdade o caso não é tão simples assim…. vejamos:

O problema

• É comum você precisar criar índices com várias colunas ao mesmo tempo. Em chaves naturais isso ocorre com frequência e é quase obrigatório em relações de “N para N”, quando uma tabela intermediária recebe como chave primária (vou abreviar aqui como PK, ou Primary Key) as chaves de duas tabelas, como a famosa tabela “detalhes_pedido” com as chaves da tabela “pedido” e “produto”.
• A ordem dos tratores altera completamente o viaduto. Se você tem as colunas A, B e C num índice composto, você tem de criar o índice com numa ordem específica: da coluna com menor variação de dados, para a coluna com maior variação de dados.
• Vamos entender que variação aqui, é a quantidade de valores diferentes que uma coluna pode assumir em relação ao número de registros da tabela. Uma coluna que sozinha responde pela PK tem 100% de variação, pois todos os registros tem valores distintos. Uma tabela que possui apenas 2 valores distintos numa tabela com milhares de registros, tem 50% de variação.
• No catálogo do PostgreSQL (o PG_CATALOG), temos 2 lugares que mostram a variação de uma coluna: pg_statistics e a visão sobre esta tabela que é a pg_stats . Estas tabelas são alimentadas pela rotina de VACUUM ANALYZE.
• Índices criados com uma ordem errada, onde colunas com variação baixa são colocadas no final do comando CREATE INDEX ou na definição de uma PK ou UK (que criam índices implícitos com na mesma ordem da restrição PK ou UK) vão resultar num índice ineficiente. Esta ineficiência se traduz em desperdício de espaço em disco, maior sentidão no processo de INDEX SCAN e há terríveis casos onde uma consulta com um filtro (leia-se WHERE) num dos campos do índice não vai conseguir utilizar aquele índice.
• Este problema não é uma particularidade do PostgreSQL, ele ocorre nos demais SGDBs também. Infelizmente a maneira de detectar o problema é diferente em cada um, pois o acesso à este tipo de informação não está presente no Information Schema, que faz parte do padrão SQL;
• Na tabela pg_statistic a coluna ‘stadistinct’ que equivale à coluna n_distinct na visão pg_stats. O significado da coluna não é tão trivial. Vejamos:
  • Se o valor for 0 (zero), então significa que não foi coletada nenhuma informação no ANALYZE. Rode esta rotina e depois verifique novamente;
  • Se o valor for positivo, então ele indica o número de valores diferentes que a coluna recebe. O valor positivo também significa que ao rodar o ANALYZE, o PostgreSQL acredita (ele pode errar) que o número de valores é fixo, ou seja, são sempre os mesmos valores nas colunas, independente do número de registros.
  • Se o valor for negativo, então você tem o número de valores distintos naquela coluna já dividido pelo número de registros da tabela.

Verificando as estatísticas de um único índice

Bom, então eu criei uma consulta para verificar como estão as estatísticas de cada coluna de um índice específico:

SELECT 
    n.nspname    AS "Schema", 
    c2.relname   AS "Table", 
    c.relname    AS "Index",
    TO_CHAR(c.reltuples,'999G999G999') AS num_rows,
    c.relnatts   AS col_count, 
    a.attnum     AS Ord,
    a.attname    AS "Column",
    TO_CHAR(CASE WHEN s.n_distinct = 0 THEN 0
             WHEN s.n_distinct < 0 THEN @ 100 * s.n_distinct
             WHEN s.n_distinct > 0 THEN 100 * s.n_distinct / c2.reltuples
             END ,'000D99')      AS "%Var",
    format_type(a.atttypid, a.atttypmod) AS "Type"
FROM
        pg_class          AS c
        JOIN pg_namespace AS n ON n.oid = c.relnamespace
        JOIN pg_index     AS i ON c.oid = i.indexrelid
        JOIN pg_class     AS c2 ON c2.oid = indrelid
        JOIN pg_attribute AS a ON a.attrelid = i.indexrelid
        JOIN pg_stats AS s ON 
            s.tablename = c2.relname AND s.attname = a.attname
    WHERE
        n.nspname NOT IN ('pg_catalog','information_schema') AND
        a.attnum > 0 AND 
        NOT a.attisdropped  AND
        c.relnatts > 0 AND
        c.relname = 'nome_do_índice'
ORDER BY a.attnum;

Vejamos como esta consulta fica com o índice film_actor_pkey da base PAGILA:

Schema |   Table    |      Index      |   num_rows   | col_count | ord |  Column  |  %Var   |   Type
-------+------------+-----------------+--------------+-----------+-----+----------+---------+----------
public | film_actor | film_actor_pkey |        5.462 |         2 |   1 | actor_id |  003,66 | smallint
public | film_actor | film_actor_pkey |        5.462 |         2 |   2 | film_id  |  018,25 | smallint

Aqui notamos que o índice está OK, pois a 1ª coluna tem ~3,7% de variação e a 2ª tem ~18%.

Verificando todos índices com ordem trocada na base

O problema é que eu queria verificar todos os índices (tirando os índices do próprio catálogo) que possuem índices onde a ordem está invertida. Então eu montei a consulta abaixo para me ajudar:

WITH multi_col AS (SELECT 
    i.indexrelid AS index_oid,
    n.nspname    AS schema,
    c2.relname   AS TABLE,
    c2.reltuples AS row_num,
    c.relname    AS INDEX,
    a.attnum     AS n_col,
    a.attname    AS col,
    CASE WHEN s.n_distinct = 0 THEN 0
        WHEN s.n_distinct < 0 THEN @ s.n_distinct
        WHEN s.n_distinct > 0 THEN s.n_distinct / c2.reltuples
         END AS var
  FROM 
    pg_class AS c
    JOIN pg_namespace AS n  ON n.oid = c.relnamespace
    JOIN pg_index     AS i  ON c.oid = i.indexrelid
    JOIN pg_class     AS c2 ON c2.oid = indrelid
    JOIN pg_attribute AS a  ON a.attrelid = i.indexrelid
    JOIN pg_stats     AS s  ON s.tablename = c2.relname AND s.attname = a.attname
  WHERE
    n.nspname NOT IN ('pg_catalog','information_schema') AND
    a.attnum > 0 AND 
    NOT a.attisdropped  AND
    c.relnatts > 1
), wrong_col AS (SELECT 
    col_a.index_oid,
    col_a.col
  FROM
    multi_col AS col_a
    JOIN multi_col AS col_b ON
    col_a.index_oid = col_b.index_oid AND
    col_a.n_col = col_b.n_col + 1 AND
    col_a.var < col_b.var)
SELECT DISTINCT
    col.schema,
    col.TABLE,
    col.INDEX,
    TO_CHAR(col.row_num,'999G999G999'),
    col.n_col,
    col.col || CASE wrong_ind_col.col WHEN col.col THEN ' ***' ELSE '' END AS col,
    TO_CHAR(col.var,'0D99999') AS var,
    pg_get_indexdef(col.index_oid) AS "CREATE"
  FROM
    multi_col AS col
    JOIN wrong_col AS wrong_index ON col.index_oid = wrong_index.index_oid
    LEFT JOIN wrong_col AS wrong_ind_col ON 
        col.index_oid = wrong_ind_col.index_oid AND 
        col.col = wrong_ind_col.col
  WHERE row_num > 1000
  ORDER BY
    col.schema,
    col.TABLE,
    col.INDEX, 
    col.n_col;

Observações

• Esta consulta utiliza um pouco de CTE para facilitar, portanto só funciona a partir do PostgreSQL 8.4.
• Cuidado!!! Estas consultas não fazem o menor sentido se o ANALYZE não estiver atualizado e se você não está utilizando dados reais na sua base. Se você tem uma base de testes com dados fictícios, então você tem de confiar na sua capacidade de modelagem quando você prevê qual coluna tem variação maior.
• Eu coloquei no meio desta consulta um filtro para pegar apenas os índices de tabelas com mais de mil registros. Índices de tabelas com poucos registros não são utilizados de qualquer forma numa consulta, então eu resolvi ignora-las.

De qualquer forma, a consulta é uma ferramenta para ajudar o DBA a detectar uma possível falha de modelagem que pode estar causando problemas de performance.

Dicas para melhorar / corrigir as consultas são bem vindas.

Por hoje é só.