Por que uma ligeira alteração no termo de pesquisa atrasa tanto a consulta?
Eu tenho a seguinte consulta no PostgreSQL (9.5.1):
select e.id, (select count(id) from imgitem ii where ii.tabid = e.id and ii.tab = 'esp') as imgs,
e.ano, e.mes, e.dia, cast(cast(e.ano as varchar(4))||'-'||right('0'||cast(e.mes as varchar(2)),2)||'-'|| right('0'||cast(e.dia as varchar(2)),2) as varchar(10)) as data,
pl.pltag, e.inpa, e.det, d.ano anodet, coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')' determinador, d.tax, coalesce(v.val,v.valf)||' '||vu.unit as altura,
coalesce(v1.val,v1.valf)||' '||vu1.unit as DAP, d.fam, tf.nome família, d.gen, tg.nome gênero, d.sp, ts.nome espécie, d.inf, e.loc, l.nome localidade, e.lat, e.lon
from esp e
left join det d on e.det = d.id
left join tax tf on d.fam = tf.oldfam
left join tax tg on d.gen = tg.oldgen
left join tax ts on d.sp = ts.oldsp
left join tax ti on d.inf = ti.oldinf
left join loc l on e.loc = l.id
left join pess p on p.id = d.detby
left join var v on v.esp = e.id and v.key = 265
left join varunit vu on vu.id = v.unit
left join var v1 on v1.esp = e.id and v1.key = 264
left join varunit vu1 on vu1.id = v1.unit
left join pl on pl.id = e.pl
WHERE unaccent(TEXT(coalesce(p.abrev,'')||' ('||coalesce(p.prenome,'')||')')) ilike unaccent('%vicen%')
São necessários 430 ms para recuperar 1129 linhas de um total de 9250 emesp
mesa.
Se eu alterar o termo de pesquisa de%vicen%
para%vicent%
(adicionando um 't'), são necessários 431ms para recuperar as mesmas 1129 linhas.
Ordenando pela coluna de pesquisa, crescente e decrescente, vejo que todas as 1129 linhas têm exatamente o mesmo nome nos dois casos.
Agora o estranho: se eu mudar o termo de pesquisa de%vicent%
para%vicenti%
(adicionando um 'i'), agora é inacreditável24,4 segundos recuperar as mesmas 1129 linhas!
O termo pesquisado está sempre no primeirocoalesce
, ou seja,coalesce(p.abrev,'')
. Espero que a consulta seja mais lenta ou mais rápida, dependendo do tamanho da string pesquisada, mas não tanto! Alguém tem alguma idéia do que está acontecendo?
Resultados deEXPLAIN ANALYZE
(excederia o limite de 30k caracteres aqui):
Para%vicen%
: http://explain.depesz.com/s/2XF
Para%vicenti%
: http://explain.depesz.com/s/dEc6