[...]
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.
So while the above is all true I apparently mis-understood your question... :(
I'm going to wait for someone thinking more clearly to answer...but it seems that given an inability to prove that the result of the function call is meaningfully selective the system would default to choosing a sequential scan plan over an index. You happen to choose a value that only returns a single row but nothing prevents you from picking one that returns the entire table. There may be other factors involved as I am not that familiar with the full text search capabilities of PostgreSQL.
David J.
