Обсуждение: tsearch vs. fulltextindex

Поиск
Список
Период
Сортировка

tsearch vs. fulltextindex

От
"Christopher Kings-Lynne"
Дата:
Hi,

I've just done some performance comparisons between contrib/fulltextindex
and contrib/tsearch.  Even with every optimisation I can think of for
fulltextindex, tsearch is 300 times faster ;)

Plus it doesn't require a separate table or complicated queries.

I think we should strongly encourage people to use tsearch instead of
fulltextindex.  I hope to commit some change to fulltextindex in the near
future, so I'll add a note to the readme then.

Chris

eg:

australia=# explain analyse select food_id, category_id, description from
test_foods where not pending and fulltextidx ## 'baskin&fruit';
NOTICE:  QUERY PLAN:

Index Scan using fulltextidx_idx on test_foods  (cost=0.00..45.93 rows=11
width=40) (actual time=0.22..1.53 rows=8 loops=1)
Total runtime: 1.70 msec

EXPLAIN
australia=# explain analyze SELECT distinct(f.food_id), f.category_id,
f.description, f.brand FROM food_foods f, food_foods_fti f0, food_foods_fti
f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND
f1.id=f.oid AND f1.string ~ '^fruit';
NOTICE:  QUERY PLAN:

Unique  (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25
rows=8 loops=1)
  ->  Sort  (cost=12.10..12.10 rows=1 width=66) (actual time=532.10..532.14
rows=8 loops=1)
        ->  Nested Loop  (cost=0.00..12.09 rows=1 width=66) (actual
time=292.41..531.89 rows=8 loops=1)
              ->  Nested Loop  (cost=0.00..6.07 rows=1 width=8) (actual
time=292.35..531.35 rows=8 loops=1)
                    ->  Index Scan using food_foods_fti_string_idx on
food_foods_fti f0  (cost=0.00..3.03 rows=1 width=4) (actual time=0.07..0.45
rows=23 loops=1)
                    ->  Index Scan using food_foods_fti_string_idx on
food_foods_fti f1  (cost=0.00..3.03 rows=1 width=4) (actual time=0.04..16.52
rows=1092 loops=23)
              ->  Index Scan using food_foods_oid_idx on food_foods f
(cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 rows=1 loops=8)
Total runtime: 532.49 msec

EXPLAIN

Re: [HACKERS] tsearch vs. fulltextindex

От
Bruce Momjian
Дата:
Good point.  Some said fulltextindex was better for certain queries, but
if no one can come up with such a case, we can remove it.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> Hi,
>
> I've just done some performance comparisons between contrib/fulltextindex
> and contrib/tsearch.  Even with every optimisation I can think of for
> fulltextindex, tsearch is 300 times faster ;)
>
> Plus it doesn't require a separate table or complicated queries.
>
> I think we should strongly encourage people to use tsearch instead of
> fulltextindex.  I hope to commit some change to fulltextindex in the near
> future, so I'll add a note to the readme then.
>
> Chris
>
> eg:
>
> australia=# explain analyse select food_id, category_id, description from
> test_foods where not pending and fulltextidx ## 'baskin&fruit';
> NOTICE:  QUERY PLAN:
>
> Index Scan using fulltextidx_idx on test_foods  (cost=0.00..45.93 rows=11
> width=40) (actual time=0.22..1.53 rows=8 loops=1)
> Total runtime: 1.70 msec
>
> EXPLAIN
> australia=# explain analyze SELECT distinct(f.food_id), f.category_id,
> f.description, f.brand FROM food_foods f, food_foods_fti f0, food_foods_fti
> f1 WHERE NOT f.pending AND f0.id=f.oid AND f0.string ~ '^baskin' AND
> f1.id=f.oid AND f1.string ~ '^fruit';
> NOTICE:  QUERY PLAN:
>
> Unique  (cost=12.10..12.11 rows=1 width=66) (actual time=532.11..532.25
> rows=8 loops=1)
>   ->  Sort  (cost=12.10..12.10 rows=1 width=66) (actual time=532.10..532.14
> rows=8 loops=1)
>         ->  Nested Loop  (cost=0.00..12.09 rows=1 width=66) (actual
> time=292.41..531.89 rows=8 loops=1)
>               ->  Nested Loop  (cost=0.00..6.07 rows=1 width=8) (actual
> time=292.35..531.35 rows=8 loops=1)
>                     ->  Index Scan using food_foods_fti_string_idx on
> food_foods_fti f0  (cost=0.00..3.03 rows=1 width=4) (actual time=0.07..0.45
> rows=23 loops=1)
>                     ->  Index Scan using food_foods_fti_string_idx on
> food_foods_fti f1  (cost=0.00..3.03 rows=1 width=4) (actual time=0.04..16.52
> rows=1092 loops=23)
>               ->  Index Scan using food_foods_oid_idx on food_foods f
> (cost=0.00..6.01 rows=1 width=58) (actual time=0.03..0.04 rows=1 loops=8)
> Total runtime: 532.49 msec
>
> EXPLAIN
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: tsearch vs. fulltextindex

От
"Graeme Merrall"
Дата:
> I've just done some performance comparisons between contrib/fulltextindex
> and contrib/tsearch.  Even with every optimisation I can think of for
> fulltextindex, tsearch is 300 times faster ;)
>
> Plus it doesn't require a separate table or complicated queries.
>
> I think we should strongly encourage people to use tsearch instead of
> fulltextindex.  I hope to commit some change to fulltextindex in the near
> future, so I'll add a note to the readme then.
>

Ditto. We just replaced Oracle Context Search with tsearch and it's just as
good for what we were using it for and nicer to play with.
There's no scoring as far as I can see but I can live with that.

(and hello fellow aussie)

Cheers,
 Graeme