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)
--
