On 12/29/15 5:21 PM, David G. Johnston wrote:
> 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 not an expert on text search, but presumably the tsvector knows what
config was used, which means as long as the config is part of the index
it should be OK.
Marking to_tsquery_partial as immutable would be wrong and potentially
dangerous, as you can change default_text_search_config at any time,
which would change the output of to_tsquery (as well as to_tsvector).
> 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.
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?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
