Eu tenho um banco de dados PostgreSQL com várias colunas de string em uma tabela com um índice trigrama usando pg_trgm. Eles são questionados assim
SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%';
Para consultas com termos de pesquisa muito específicos, isso funciona muito bem. Existem algumas centenas de milhares de linhas nessa tabela, e algumas dessas colunas podem conter uma grande quantidade de texto (embora a maioria delas sejam strings razoavelmente curtas).
O problema é que isso não funciona bem quando os termos de pesquisa são comuns e aparecem em muitas linhas. Para consultas de pesquisa com termos de pesquisa específicos, essa consulta é concluída em milissegundos; para termos que correspondem a aproximadamente 80% de todas as linhas, leva dezenas de segundos. Os índices trigramas são usados em quase todas as consultas, mesmo as lentas.
Eu sei que isso provavelmente é inerente ao modo como esse índice funciona e não pode ser facilmente corrigido, mas estou procurando principalmente maneiras de lidar melhor com esses casos do que apenas esperar o tempo limite da consulta. De qualquer forma, pesquisar esses termos não é muito útil, pois não filtrará linhas suficientes. Mas não posso impedir que os usuários insiram esses termos, então gostaria de falhar com um pouco mais de elegância do que com um tempo limite de consulta, se possível.
Primeiro, há alguma maneira de aumentar o desempenho desse tipo de consulta? Existem tipos melhores de índices para esta ou outras maneiras de ajustar o desempenho do índice? Estou usando intencionalmente índices de trigramas aqui e não a pesquisa de texto completo do Postgres, pois a lematização seria contraproducente aqui. Os termos de pesquisa geralmente não são palavras em inglês e, em meus primeiros experimentos, a derivação foi prejudicial para certas consultas, pois, por exemplo, cortou partes de abreviações ou termos específicos do domínio.
Existe uma maneira de detectar casos como este em que o termo de pesquisa é excessivamente inespecífico? Se não for possível fazer essas consultas com rapidez suficiente, a próxima melhor coisa seria não executá-las e atingir o tempo limite, mas mostrar ao usuário um feedback útil muito mais cedo de que seus termos de pesquisa são problemáticos.
