Re: cannot get stable function to use index - Mailing list pgsql-general

On 12/29/2015 6:35 PM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.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