Re: cannot get stable function to use index

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


The Others:

max_connections = 20
shared_buffers = 400MB
work_mem = 5MB
maintenance_work_mem = 64MB
effective_cache_size = 1700MB
synchronous_commit = off
effective_io_concurrency = 3
track_io_timing = on
max_locks_per_transaction = 2300


I can't honestly say why I've set random_page_cost.  Its been way too
long for me to remember.  The box is running a 4 drive sata software
raid 10, on Slackware64.

-Andy