From: Ivan Voras <ivoras@freebsd.org>
To: pgsql-performance@postgresql.org
Subject: Re: Understanding tsearch2 performance
Date: Wed, 14 Jul 2010 16:21:56 +0200
Message-ID: <i1kh5h$asl$1@dough.gmane.org> (raw)
In-Reply-To: <4C3D7D5E0200002500033548@gw.wicourts.gov>
References: <4C3D7CE10200002500033545@gw.wicourts.gov>
<4C3D7D5E0200002500033548@gw.wicourts.gov>
<4C3D7D5E0200002500033548@gw.wicourts.gov>
On 07/14/10 16:03, Kevin Grittner wrote:
> Ivan Voras < ivoras@freebsd.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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ivoras@freebsd.org
Subject: Re: Understanding tsearch2 performance
In-Reply-To: <i1kh5h$asl$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox