Re: cannot get stable function to use index

On 12/30/2015 2:03 PM, Andy Colson wrote:
> On 12/30/2015 1:55 PM, Tom Lane wrote:
>> Andy Colson <andy@squeakycode.net> writes:
>>> On 12/30/2015 1:07 PM, Tom Lane wrote:
>>>> it seems like you've got some weird data statistics that are causing a
>>>> misestimate.  Could we see the pg_stats row for that tsvector column?
>>>> Or maybe even the actual data?
>>
>>> The table exists in a schema named jasperia, I've been removing the name
>>> for simplicity.  The dump of the table is here:
>>
>> Thanks very much for sharing the data.  But now I am well and truly
>> confused, because I still can't reproduce your results.  I get
>>
>> regression=# explain analyze
>> select *
>> from search
>> where search_vec @@ to_tsquery('213 & E & 13 & ST & N:*');
>>                                                       QUERY PLAN
>> --------------------------------------------------------------------------------------------------------------------
>>
>>   Bitmap Heap Scan on search  (cost=76.00..80.02 rows=1 width=72)
>> (actual time=8.119..8.119 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..76.00 rows=1
>> width=0) (actual time=8.113..8.113 rows=1 loops=1)
>>           Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST &
>> N:*'::text))
>>   Total runtime: 8.210 ms
>>
>> This is on 9.3 branch tip, not 9.3.9 which I don't have installed;
>> but I see no bug fixes related to GIN estimation in the commit logs
>> since 9.3.9.
>>
>> Are you using any nondefault planner settings?  Anything else
>> unusual about your installation?
>>
>>             regards, tom lane
>>
>
> There are others, but I'll bet its:
>
> random_page_cost = 1
>

Humm, nope.  I removed the config option, restart PG, then analyzed the
search table:

# show random_page_cost ;
  random_page_cost
------------------
  4

# analyze search;

And it still wont use the index.  I'll tool around a little more and see
if I can find something.

Thanks much for all your help on this.

-Andy