[...]
Originally it didn't have "STABLE STRICT", but I added it. Doesn't seem to matter though. I cannot get this sql to use the index:
explain analyze
select *
from search
where search_vec @@ to_tsquery_partial('213 E 13 ST N')
--------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2526.56 rows=1 width=69) (actual time=68.033..677.490 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) || ':*'::text)))
Rows Removed by Filter: 76427
Total runtime: 677.548 ms
(4 rows)
to_tsquery_partial() calls to_tsquery() and array_to_string(), both of which I checked, and all of them are marked as stable.
STABLE functions, nor VOLATILE ones, are candidates for indexing. Only IMMUTABLE ones. The default for functions is VOLATILE.
I haven't the time to provide a solution to your problem - I'm just pointing out "cannot get stable function to use index" is working as designed and as is logically required. An index must not rely upon outside information, most typically time, since there exists no means for an index to update itself based upon changes in the environment. The only type of function guaranteed to not rely upon the external environment is an immutable one. And no, you shouldn't lie by marking a function immutable to get this to work. The system does not check that the stated volatility and the actual implementation match.
David J.
