Re: cannot get stable function to use index

On 12/29/2015 6:03 PM, Jim Nasby wrote:
>
> If I'm reading EXPLAIN ANALYZE correctly, to_tsquery_partial is being
> simplified out of the query entirely:
>
> Filter: (search_vec @@
> to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) ||
> ':*'::text)))
>
> Part of this could well be that you're not feeding the same data to
> to_tsquery. Your hard-coded example is
>
> where search_vec @@ to_tsquery('213 & E & 13 & ST & N');
>
> but your second query becomes '213 & E & 13 & ST & N:*'. Have you tried
> that as a hard-coded value?

Ahh! Yep, that was the missing link. Jeez, I can't believe I couldn't
find it. The :* is for matching partials, its even IN the name
to_tsquery_partial.

Indeed, this does not use an index:

explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*')

Thank you!

-Andy