By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,782 Members | 1,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,782 IT Pros & Developers. It's quick & easy.

FTI problems

P: n/a
I am trying to use the fti module to search my text.
Searching through the raw text using ILIKE takes 3 seconds,
searching using fti takes 212 seconds.
Then i tried to turn off seq_scan to see what happens, the
planner still does a seq_scan.
Why does the planner not use the index?

Are there any other text indexing modules i should be looking at?
Tsearch2 is excellent, but doesn't do substring matches (which i must
have).

Thanks!

Ps. I had to edit the fti.pl code in order to get it to run on RH9. If
anyone else would like it let me know.

date=# VACUUM FULL ANALYZE;
VACUUM
data=# EXPLAIN ANALYZE SELECT DISTINCT * FROM data_fti WHERE string ~ '^
ball';
QUERY PLAN

------------------------------------------------------------------------
---------------------------------------------------
------------
Unique (cost=912338.97..912339.30 rows=4 width=18) (actual time=212654.
55..212663.67 rows=4496 loops=1)
-> Sort (cost=912338.97..912339.08 rows=43 width=18) (actual time=
212654.54..212656.91 rows=4496 loops=1)
Sort Key: string, id
-> Seq Scan on data_fti (cost=0.00..912337.80 rows=43 width=18) (
actual time=75855.20..212586.13 rows=44
96 loops=1)
Filter: (string ~ '^ball'::text)
Total runtime: 212677.70 msec
(6 rows)

data=# \d data_fti_string_idx
Index "public.data_fti_string_idx"
Column | Type
--------+------
string | text
btree, for table "public.data_fti"

collection=# set enable_seqscan TO off;
SET
collection=# EXPLAIN ANALYZE SELECT DISTINCT * FROM data_fti WHERE
string ~ '^ball';
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
--
Unique (cost=100912338.97..100912339.30 rows=4 width=18) (actual
time=171814.04..171823.37 rows=4496 loops=1)
-> Sort (cost=100912338.97..100912339.08 rows=43 width=18) (actual
time=171814.04..171816.36 rows=4496 loops=1)
Sort Key: string, id
-> Seq Scan on data_fti (cost=100000000.00..100912337.80 rows=43
width=18) (actual time=36496.54..171743.01 rows=4496 loops=1)
Filter: (string ~ '^ball'::text)
Total runtime: 171826.04 msec
(6 rows)
--

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.