Melhore as consultas que não diferenciam maiúsculas de minúsculas no Postgres usando índices mais inteligentes

Postgres é o banco de dados de escolha do Coderwall. Não apenas porque é oficialmente compatível e integrado ao Heroku, mas também porque é uma ferramenta muito poderosa. Um dos recursos exclusivos do Postgres é seu poderoso mecanismo de índice. Usando o Postgres, você pode obter um controle muito mais refinado sobre como seus dados são indexados. Por exemplo, índices em expressões que permitem criar índices em campos que têm as funções UPPER ou LOWER aplicadas a eles.

Normalmente, um campo VARCHAR é tratado com distinção entre maiúsculas e minúsculas no Postgres. O que significa que fieldname = 'FOO'e fieldname = 'foo'não encontrará nenhuma correspondência quando o valor real for “FOO”. Uma técnica comum é normalizar o valor do campo, como lower(fieldname) = lower('FOO')forçar os campos a corresponderem usando seu formulário em minúsculas. O problema é que isso irá ignorar completamente os índices e fazer com que o Postgres use uma varredura sequencial para encontrar a correspondência. Cada linha será avaliada em busca de uma correspondência. Isso é obviamente caro e não ideal para tabelas grandes e frequentemente consultadas.

postgresql=> explain select username from users where lower(username) = lower('just3ws');
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on users  (cost=0.00..13327.36 rows=558 width=9)
   Filter: (lower((username)::text) = 'just3ws'::text)
(2 rows)

Varredura de Seq nos usuários (custo = 0,00..13327,36 linhas = 558 largura = 9)

Compare isso com a consulta sem expressão e um índice simples no campo de nome de usuário.

postgresql=> explain select username from users where username = lower('just3ws');
                                     QUERY PLAN
-------------------------------------------------------------------------------------
 Index Scan using index_users_on_username on users  (cost=0.00..4.07 rows=1 width=9)
   Index Cond: ((username)::text = 'just3ws'::text)
(2 rows)

Index Scan usando index_users_on_username em usuários (custo = 0,00..4,07 linhas = 1 largura = 9)

Obviamente, há um custo enorme a pagar ao consultar expressões. Mas tudo não está perdido. O Postgres permitirá que você crie índices em relação à expressão .

Neste caso, aplicaremos um índice que levará em consideração a expressão.lower()

create index ix_users_username_lower on users (lower(username) varchar_pattern_ops);

Agora, quando consultarmos , poderemos aproveitar as vantagens do índice para evitar a varredura completa da tabela.lower(usernames)

postgresql=> explain select username from users where lower(username) = lower('just3ws');
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on users  (cost=4.92..988.43 rows=558 width=9)
   Recheck Cond: (lower((username)::text) = 'just3ws'::text)
   ->  Bitmap Index Scan on ix_users_username_lower  (cost=0.00..4.90 rows=558 width=0)
         Index Cond: (lower((username)::text) = 'just3ws'::text)
(4 rows)

Varredura de heap de bitmap nos usuários (custo = 4,92..988,43 linhas = 558 largura = 9)

É muito melhor do que a varredura sequencial agora, embora ainda um pouco mais lento do que apenas consultar o índice simples.

O Bitmap Heap Scan é útil quando há muita variedade nos dados e o Postgres é capaz de segregar os dados de maneira inteligente. Basicamente, os dados são agrupados em conjuntos menores que o Postgres pode filtrar e decidir de forma mais inteligente quais conjuntos devem ser verificados e quais ignorar. Onde a varredura sequencial tocará em cada linha da tabela.

Leia mais sobre o Índice Postgres em Expressões na documentação oficial e esta explicação do autor do @sss .pgh.pa.us “rel =” nofollow “> algoritmo de varredura de heap de bitmap Postgres Tom Lane.

Para uma explicação completa do que a cost, rowse widthvalores médios confira Usando Explique a partir da documentação do PostgreSQL.

varchar_pattern_opsé explicado na documentação do Índice Postgres .