Como consultar com eficiência do Postgres para selecionar palavras especiais?

Uma abordagem fácil seria considerar a matriz de letras correspondente a cada palavra e pesquisar dentro dela com o @>operador de matriz (contém). Isso funciona independentemente das posições das letras, conforme mostrado no exemplo do manual , ou seja, ARRAY[1,4,3] @> ARRAY[3,1]é verdade.

Esta matriz pode ser facilmente obtida com regexp_split_to_array(name, '').
[ EDIT : de acordo com a resposta de @Erwin , string_to_array(name, NULL)é mais rápido, então é melhor usar isso. É uma substituição imediata no resto da resposta]

Aqui está uma demonstração que primeiro materializa a matriz como uma coluna em uma tabela de teste contendo uma mistura de palavras em inglês e francês (~ 511.000 linhas, comprimento médio = 13 caracteres) e, em seguida, uma segunda tabela de teste sem adicionar a matriz como uma coluna.

=> CREATE TABLE tstword AS
    SELECT word_id as id,
    wordtext as name,
    regexp_split_to_array(wordtext, '') as arr FROM words;

Para encontrar um número relativamente grande de palavras:

=> select count(*) from tstword where arr @> array['s','m','e'];
 count 
-------
 42268
(1 row)

Time: 268.809 ms

Isso faz uma varredura sequencial conforme mostrado por EXPLAIN ANALYZE:

 explain analyze select name from tstword where arr @> array['s','m','e'];
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Seq Scan on tstword  (cost=0.00..17554.46 rows=21256 width=14) (actual time=0.020..268.525 rows=42268 loops=1)
   Filter: (arr @> '{s,m,e}'::text[])
   Rows Removed by Filter: 468729
 Total runtime: 269.927 ms
(4 rows)

Time: 270.414 ms

Mas podemos indexar o array com um índice GIN:

CREATE INDEX idx_tst on tstword using gin(arr);

E então é bem mais rápido:

explain analyze select name from tstword where arr @> array['s','m','e'];
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword  (cost=252.74..11815.73 rows=21256 width=14) (actual time=46.378..60.203 rows=42268 loops=1)
   Recheck Cond: (arr @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst  (cost=0.00..247.42 rows=21256 width=0) (actual time=45.202..45.202 rows=42268 loops=1)
         Index Cond: (arr @> '{s,m,e}'::text[])
 Total runtime: 61.677 ms
(5 rows)

Time: 70.185 ms

Podemos até evitar a materialização do array como uma coluna indexando diretamente a expressão, já que o postgres suporta índices funcionais.

create table tstword2 as select word_id as id,wordtext as name from words;
create index idx_tst2 on tstword2  using gin(regexp_split_to_array(name, ''));

Em seguida, a pesquisa deve ser feita exatamente com a mesma expressão e o índice é usado:

 explain analyze select name from tstword2 where regexp_split_to_array(name, '') @> array['s','m','e'];
                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tstword2  (cost=40.00..44.02 rows=1 width=14) (actual time=39.390..48.435 rows=42268 loops=1)
   Recheck Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
   ->  Bitmap Index Scan on idx_tst2  (cost=0.00..40.00 rows=1 width=0) (actual time=39.053..39.053 rows=42268 loops=1)
         Index Cond: (regexp_split_to_array((name)::text, ''::text) @> '{s,m,e}'::text[])
 Total runtime: 49.748 ms
(5 rows)

Time: 50.193 ms

Consulte Tipos de índices GiST e GIN no manual para obter advertências sobre esses tipos de índices.

Caso de teste

Eu construí um caso de teste meio realista:

CREATE TABLE word(word_id int, word text);
INSERT INTO word (word_id, word)
SELECT g%(2500000/25) -- max length 25
      ,left(string_agg(chr(97 + (random()^2 * 31)::int), ''), 3 + (random()^2 * 25)::int)
FROM  (SELECT generate_series(1, 2500000) AS g) g
GROUP  BY 1
ORDER  BY 1;  --> 100k rows

Palavras minúsculas com 3 a 25 letras, alguns caracteres adicionais como substitutos para letras não ASCII. Palavras mais curtas são mais comuns e algumas letras são mais comuns do que outras. Usando random()^2para obter uma distribuição de dados inclinada.

Usando isso, executei vários testes para comparar a abordagem de @Daniel com algumas alternativas.

word LIKE ALL(arr)

Achei esta consulta surpreendentemente eficaz, mesmo sem índice:

SELECT * FROM word
WHERE  word LIKE ALL('{%l%,%w%,%x%,%y%,%z%,%~%}'::text[]);

Para fazê-lo funcionar, você preencheria suas letras com %. Ou seja: a-> %a%e forme um array como acima.

Quase tão rápido com uma varredura sequencial em 100 mil linhas quanto outras soluções baseadas em um índice GIN. Também testei com 10k e 40k linhas e no Postgres 9.1. Resultados semelhantes.

Isso vai se deteriorar com mesas ainda maiores. Mas se sua tabela contiver 100 mil linhas ou menos (e não tiver outras colunas grandes), essa consulta simples pode ser tudo o que você precisa.

índice funcional

string_to_array()

Em primeiro lugar, use string_to_array(word, NULL)) em vez de regexp_split_to_array(word, ''). Achei 5 a 6 vezes mais rápido no Postgres 9.1 e 2 a 3 vezes mais rápido no Postgres 9.3. Afeta principalmente a manutenção do índice, mas também, em menor grau, o desempenho da consulta.

Qualquer uma dessas expressões resulta em uma matriz não classificada com possíveis duplicatas.

CREATE INDEX word_arr_gin_idx ON word USING gin(string_to_array(word, NULL));

variantes ineficazes

A remoção de duplicatas da matriz é ineficaz, pois o algoritmo GIN faz isso sozinho. Citando o manual:

Cada valor de chave é armazenado apenas uma vez, portanto, um índice GIN é muito compacto para casos em que a mesma chave aparece várias vezes.

Incluo essa variante ineficaz no violino de qualquer maneira para provar o ponto e também como prova de conceito - como usar uma IMMUTABLEfunção definida pelo usuário em um índice:

CREATE OR REPLACE FUNCTION uniq_arr(text)
  RETURNS text[] AS
$func$
SELECT ARRAY(
   SELECT DISTINCT i
   FROM   unnest(string_to_array($1, NULL)) t(i))
$func$ LANGUAGE sql IMMUTABLE;

CREATE INDEX word_uniq_arr_gin_idx ON word USING gin(uniq_arr(word));

Um índice em uma matriz distinta e classificada também mostra os mesmos tempos de pesquisa, embora seja mais caro. Eu não incluí.

-> Demonstração do SQLfiddle.

Dobrar letras

No entanto, uma IMMUTABLEfunção como a demonstrada acima pode ser usada para dobrar letras. Um caso de uso típico seria com o unaccentmódulo para remover acentos (sinais diacríticos), ou seja, também localizar é, èetc. ao procurar e.

Esta seria uma solução muito eficaz:

CREATE OR REPLACE FUNCTION unaccent_arr(text) RETURNS text[] AS
$func$
SELECT string_to_array(unaccent('unaccent', $1), NULL)
$func$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp;

CREATE INDEX word_unaccent_arr_gin_idx ON word USING gin(unaccent_arr(word));

Explicação detalhada nesta resposta relacionada em SO (não deixe de lê-la):
O PostgreSQL oferece suporte a agrupamentos “insensíveis a acentos”?

Testei localmente no Postgres 9.1 e 9.3, mas não consigo incluir no violino, onde não consigo instalar módulos adicionais.

O Postgresql tem muitas opções de indexação. Ele também possui alguns componentes poderosos construídos sobre essas opções de indexação.

Um desses recursos é a pesquisa de texto completo. Consulte http://www.postgresql.org/docs/9.3/static/textsearch.html