Re: cannot get stable function to use index

On 12/30/2015 10:09 AM, Tom Lane wrote:
> Andy Colson <andy(at)squeakycode(dot)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