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

On 12/30/2015 10:09 AM, Tom Lane wrote:
> Andy Colson <andy@squeakycode.net> writes:
>>      ->  Bitmap Index Scan on search_key  (cost=0.00..6.00 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1)
>>            Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))
>
> Hmm ... so the partial case actually is significantly more expensive than
> the non-partial case: 4 msec vs .025 msec.  Still, that's about a 200x
> penalty, not the 10000x penalty the planner is ascribing to it.
>
> Thanks for the data!  I'll go moan about this on -hackers.
>
>             regards, tom lane
>

No, that's not right, the table was empty.  I rebuilt the table as it
was before, here are all three queries again:

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

                                               QUERY PLAN

-------------------------------------------------------------------------------------------------------
  Seq Scan on search  (cost=0.00..2211.08 rows=1 width=73) (actual
time=31.904..380.568 rows=1 loops=1)
    Filter: (search_vec @@ to_tsquery('213 & E & 13 & ST & N:*'::text))
    Rows Removed by Filter: 79071
  Total runtime: 380.609 ms



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

                                                       QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on search  (cost=63623.00..63624.02 rows=1 width=73)
(actual time=5.004..5.004 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..63623.00 rows=1
width=0) (actual time=4.996..4.996 rows=1 loops=1)
          Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N:*'::text))
  Total runtime: 5.045 ms


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

                                                      QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on search  (cost=23.00..24.02 rows=1 width=73)
(actual time=4.067..4.067 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..23.00 rows=1
width=0) (actual time=4.057..4.057 rows=1 loops=1)
          Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
N'::text))
  Total runtime: 4.109 ms