Re: cannot get stable function to use index

On Tue, Dec 29, 2015 at 3:52 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:

> ​[...]​
>
> 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.