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.
Você poderia iniciar a execução da consulta real fazendo um 'EXPLAIN' da consulta e, em seguida, devolver um erro ao usuário se a estimativa da linha resultante fosse muito alta para que a consulta fosse significativa.
Você pode adicionar automaticamente um LIMIT à consulta. Isso promoveria a mudança para uma varredura seq em vez de uma varredura de bitmap quando a pesquisa fosse considerada não seletiva, e então o LIMIT entraria em ação e reduziria o tempo de execução da varredura seq (mas mesmo que continuasse usando a varredura de bitmap, isso também deve ser muito mais rápido, desde que a maior parte do tempo vá para a varredura de heap de bitmap, e não para a varredura de índice de bitmap.)
Você poderia tentar definir gin_fuzzy_search_limit, mas na minha experiência isso não é confiável e parece ter erros.
Você poderia tentar descobrir por que é tão lento. Retornar 80% de algumas centenas de milhares de linhas não será muito rápido, mas provavelmente também não deve demorar 10 segundos.
Você pode simplesmente diminuir o tempo limite até o ponto em que ele expire dentro de um período de tempo razoável quando receber uma entrada incorreta.
Você poderia ter um pouco de paciência e contar com a (auto)educação do usuário. Eles provavelmente aprenderão que alguns tipos de pesquisas simplesmente não valem a pena, porque demoram muito ou retornam muitas linhas para serem úteis. Especialmente se você os ajudar a aprender isso.