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 .