Re: cannot get stable function to use index

On 12/29/2015 6:35 PM, Tom Lane wrote:
> Andy Colson <andy(at)squeakycode(dot)net> writes:
>> 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)
>
> If you force it with enable_seqscan = off, you'll soon see that it's
> capable of picking the indexscan plan, but it doesn't want to because it
> estimates that the cost will be much higher, which seems to be a
> consequence of the ":*" in the query. (Even though the functions involved
> are only stable, the planner is capable of seeing through them to look at
> the pattern that will be fed to the GIN index search.) You get the same
> results if you use the resulting tsquery without any function at all.
> For example (with dummy data), I get
>
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
> QUERY PLAN
> ----------------------------------------------------------------------------------
> Seq Scan on search (cost=0.00..3774.01 rows=1 width=21)
> Filter: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> (2 rows)
>
> regression=# set enable_seqscan TO 0;
> SET
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n:*'::tsquery;
> QUERY PLAN
> --------------------------------------------------------------------------------------------
> Bitmap Heap Scan on search (cost=104444.00..104448.01 rows=1 width=21)
> Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> -> Bitmap Index Scan on search_key (cost=0.00..104444.00 rows=1 width=0)
> Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'':*'::tsquery)
> (4 rows)
>
> but for comparison, with a pattern without ':*', I get
>
> regression=# explain select * from search
> where search_vec @@ '213 & e & 13 & st & n'::tsquery;
> QUERY PLAN
> ------------------------------------------------------------------------------------------
> Bitmap Heap Scan on search (cost=44.00..48.01 rows=1 width=21)
> Recheck Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
> -> Bitmap Index Scan on search_key (cost=0.00..44.00 rows=1 width=0)
> Index Cond: (search_vec @@ '''213'' & ''e'' & ''13'' & ''st'' & ''n'''::tsquery)
> (4 rows)
>
> I'm inclined to think this is a bug in the estimator; it seems to be
> charging for many more "entry page" fetches than there are pages in
> the index. But maybe it's right and there will be lots of repeated
> work involved. It would be interesting to see EXPLAIN ANALYZE results
> from your data for these examples.
>
> regards, tom lane
>

Here are my results, if there are any others you'd like to see please
let me know. Thanks Tom.

# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');

QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on search (cost=0.00..2144.42 rows=1 width=69) (actual
time=30.584..361.147 rows=1 loops=1)
Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
Rows Removed by Filter: 76427
Total runtime: 361.181 ms
(4 rows)

Time: 363.012 ms

# set enable_seqscan TO 0;
SET
Time: 0.185 ms

# explain analyze
select *
from search
where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on search (cost=63716.00..63717.02 rows=1 width=69)
(actual time=4.354..4.355 rows=1 loops=1)
Recheck Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
-> Bitmap Index Scan on search_key (cost=0.00..63716.00 rows=1
width=0) (actual time=4.351..4.351 rows=1 loops=1)
Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
Total runtime: 4.370 ms
(5 rows)

Time: 4.794 ms