Re: FTI is really really slow; what am I doing wrong? - Mailing list pgsql-general

Did you vacuum after
populating the tables?
If not you should do it


On Wed, Aug 22, 2001 at 11:08:55AM -0400, Paul C. wrote:
> Greetings,
>   I am trying to test out the performance of the contrib/fulltextindex
> package and I am getting horrid performance results.
> The Setup:
> I created a simple table, ST (id SERIAL, body varchar(1024), which is to be
> searched.  I created the ST_FTI table, trigger and indices as per
> instructions in the FTI readme and C file.  To populate the table, I took a
> flat text version of 'War and Peace' I found on the net, broke it up into
> sentences and inserted each sentence into ST as a row.  So I have about
> 38,000 sentences and my ST_FTI table is about 2 million rows.
> The Test:
> There is exactly one sentence (row) that has the strings 'Newton' and
> 'Kepler' in it.  That is my target.  For a straight select on ST:
>   select * from st where body ~* 'newton' and body ~* 'kepler';
> the cost is 1100.41
> BUT for an query using the FTI indices:
>   select s.* from st s, st_fti f1, st_fti f2 where f1.string
>     ~ '^kepler' and f2.string ~ '^newton' and s.oid = f1.id
>     and s.oid = f2.id;
> the cost becomes a staggering 80628.92!!!  The plans are pasted at the end
> of this message.
> Now, I have all the indices created (on id of st_fti, on string of st_fti
> and on oid of st).  I cannot figure out why this is so much worse than the
> straight query.  Indeed, the cost to look up a single string in the st_fti
> table is way high:
>   select * from st_fti where string ~ '^kepler';
> costs 36703.40, AND its doing a Seq Scan on st_fti, even though an index
> exists.
> What am I doing wrong?  Is it the sheer size of the st_fti table that is
> causing problems?  Any help would be greatly appreciated.
> Thanks,
> Paul C.
>
> FTI search
> NOTICE:  QUERY PLAN:
> Merge Join  (cost=80046.91..80628.92 rows=110 width=28)
>   ->  Sort  (cost=41827.54..41827.54 rows=19400 width=24)
>         ->  Hash Join  (cost=1992.80..40216.39 rows=19400 width=24)
>               ->  Seq Scan on st_fti f2  (cost=0.00..36703.40 rows=19400
> width=4)
>               ->  Hash  (cost=929.94..929.94 rows=34094 width=20)
>                     ->  Seq Scan on st s  (cost=0.00..929.94 rows=34094
> width=20)
>   ->  Sort  (cost=38219.37..38219.37 rows=19400 width=4)
>         ->  Seq Scan on st_fti f1  (cost=0.00..36703.40 rows=19400 width=4)
> EXPLAIN
>
> Plain search:
> NOTICE:  QUERY PLAN:
> Seq Scan on st  (cost=0.00..1100.41 rows=1 width=16)
> EXPLAIN
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)