Como fazer esta consulta usar meu índice de várias colunas?

A expressão lower(name::text)não está coberta pelo seu índice, o que também não parece ser nada útil , pelo menos pelo que vemos na sua pergunta:

CREATE INDEX crm_customer_big_index 
    ON crm_customer (name ASC, 
                     name text_pattern_ops, 
                     (id::text) text_pattern_ops, 
                     phone text_pattern_ops, 
                     mail text_pattern_ops, 
                     rating);

Este índice cobriria sua consulta:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(name::text) text_pattern_ops
, address_id);  -- for the join

Para otimizar, sugiro adaptar sua consulta adicionalmente:

SELECT * 
FROM   customer_list
WHERE  lower(name::text) LIKE 'env%' 
AND    rating = 3 
ORDER  BY lower(name::text), name
LIMIT  20;

Além disso, se você estiver interessado apenas nos primeiros caracteres de name character varying(100), torne o índice mais curto e rápido indexando apenas os primeiros caracteres:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(left(name::text, 7)) text_pattern_ops  -- example with local optimum
, address_id);  -- for the join

A consulta deve corresponder à expressão de índice:

WHERE  lower(left(name::text, 7)) LIKE 'env%' 
...
ORDER  BY lower(left(name::text, 7)), name

Não tenho certeza se a visão está atrapalhando de alguma forma. Mas deve ser transparente.

Eu não usaria varchar(n)de jeito nenhum. Apenas text:

• Devo adicionar um limite de comprimento arbitrário às colunas VARCHAR?

Sobre correspondência de padrões e text_pattern_ops:

• Correspondência de padrões com LIKE, SIMILAR TO ou expressões regulares no PostgreSQL

Por que coloquei ratingprimeiro no índice?

• Índice e desempenho de várias colunas

Existem técnicas de consulta para capitalizar melhor as colunas de índice não principais. Considere o Wiki do Postgres em "Loose indexscan" .

O planejador/otimizador escolherá usar um índice se (e somente se) as estimativas de custo sugerirem que a consulta seria executada mais rapidamente/mais barata. (Existem muitas simplificações na frase anterior, mas essa é a essência.) Isso significa que o índice deve suportar a consulta - fornecer uma maneira mais rápida de encontrar as linhas necessárias, de ordenar ou outras operações.

Ao mesmo tempo, ler o índice é basicamente a mesma operação que ler uma tabela. Agora, se o planejador descobrir que precisa ler o índice e, em seguida, ir para a tabela para buscar as linhas, ele pode decidir que isso é muito caro e usaria uma varredura sequencial.

Existem fatores que apontam para a não utilização de um índice.

1. a falta de algum adequado - na maioria dos casos, isso significa que a primeira coluna de qualquer índice não se encaixa em nenhuma WHEREcondição ou ordem de sua consulta
2. se a ordem das colunas dificultar a localização das linhas necessárias
3. se um índice for muito grande (contiver muitas colunas

(Há possivelmente outros também, mas no nosso caso, essas são as coisas importantes.)

Vamos reiterar sua consulta:

SELECT * 
  FROM customer_list
 WHERE lower(name::text) LIKE 'env%' 
       AND rating = 3 
 ORDER BY name ASC
 LIMIT 20 
 OFFSET 0;

O que é, efetivamente, seria traduzido no seguinte:

SELECT c.id,
       c.name,
       a.street,
       a.zip,
       a.city,
       a.country,
       c.phone,
       c.mail,
       c.rating,
       c.salesnote
 FROM crm_customer c
 JOIN crm_address a ON a.id = c.address_id
 WHERE lower(name::text) LIKE 'env%' 
       AND rating = 3 
 ORDER BY name ASC
 LIMIT 20 
 OFFSET 0;

A WHEREcláusula precisa localizar as linhas parcialmente com base em uma expressão. Seu índice não possui essa expressão. Este é um caso 1 na lista acima. Então ratingé a última coluna - a classificação fornecida é tão difícil de encontrar no índice quanto possível. Aqui nos deparamos com o caso 2. E, no geral, seu índice pode ser quase tão grande quanto sua tabela (sem ver a definição da tabela, isso é apenas um palpite) - é simplesmente muito caro para usar.

Se você criar o seguinte índice:

CREATE INDEX ON crm_customer ((lower(name::text) text_pattern_ops, rating, address_id); -- thanks, Erwin

há chances de ser usado. Ajuda a encontrar as linhas em crm_customer, depois dá o address_idpara usar na junção.

Se você puder mudar um pouco sua lógica e fazer uma

ORDER BY lower(name)

essa ordenação também seria facilitada pelo mesmo índice - nesse caso, você perderia a distinção entre valores como 'ambiente' e 'Ambiente'.

Notas:

• Sugiro que você leia as excelentes páginas Use The Index, Lucas . Ele fornece uma introdução agradável e completa ao uso de índices.
• Não tive o cuidado de incluir a junção em minha definição de índice. A excelente resposta de Erwin me fez perceber minha falha - agora editei, mas a fonte não é minha.