Re: Understanding tsearch2 performance

On 07/14/10 16:03, Kevin Grittner wrote:
> Ivan Voras < ivoras(at)freebsd(dot)org > wrote:
>> On 07/14/10 15:49, Stephen Frost wrote:
>
>>> Regarding the statistics, it's entirely possible that the index
>>> is *not* the fastest way to pull this data (it's nearly 10% of
>>> the table..)
>>
>> I think that what I'm asking here is: is it reasonable for
>> tsearch2 to extract 8,500 rows from an index of 90,000 rows in 118
>> ms, given that the approximately same task can be done with an
>> unindexed "LIKE" operator in nearly the same time?
>
> The answer is "yes." When it's 10% of the table, a sequential scan
> can be more efficient than an index, as Stephen indicated.

Ok, to verify this I've tried increasing statistics on the field and
running vacumm analyze full, which didn't help. Next, I've tried setting
enable_indexscan to off, which also didn't do it:

cms=> set enable_indexscan=off;
SET
cms=> explain analyze select id,title from forum where _fts_ @@
'fer'::tsquery order by id limit 10;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=363.18..363.20 rows=10 width=35) (actual
time=192.243..192.406 rows=10 loops=1)
-> Sort (cost=363.18..363.40 rows=91 width=35) (actual
time=192.229..192.283 rows=10 loops=1)
Sort Key: id
Sort Method: top-N heapsort Memory: 25kB
-> Bitmap Heap Scan on forum (cost=29.21..361.21 rows=91
width=35) (actual time=12.071..136.130 rows=8449 loops=1)
Recheck Cond: (_fts_ @@ '''fer'''::tsquery)
-> Bitmap Index Scan on forum_fts (cost=0.00..29.19
rows=91 width=0) (actual time=11.169..11.169 rows=8449 loops=1)
Index Cond: (_fts_ @@ '''fer'''::tsquery)
Total runtime: 192.686 ms
(9 rows)

Any ideas on how to verify this?