Re: cannot get stable function to use index

On Tue, Dec 29, 2015 at 4:13 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

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