Skip site navigation (1) Skip section navigation (2)

Re: Full Text Index disk space requirements

From: <typea(at)l-i-e(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Full Text Index disk space requirements
Date: 2002-11-27 02:40:38
Message-ID: 62972.12.249.229.112.1038364838.squirrel@www.l-i-e.com (view raw or flat)
Thread:
Lists: pgsql-performance
>> Wouldn't using f1.string = 'perth' be faster than f1.string ~ '^perth'
>>
> and
>> equally useful?  Or is ~ with ^ somehow actually faster than the
> seemingly
>> simple = comparison?
>
> f1.string = 'perth' would only match 'perth', while f1.string ~ '^perth'
>  would also match 'perthinent' (yes, I know this word does not exist).

D'oh!  I figured that one out in the shower this morning.  Sleep
deprivation, I guess...

But something is very wrong with what I've done...

archive=> explain SELECT article.* FROM article , article_fti as f1,
article_fti as f2  WHERE TRUE  AND (TRUE  AND  (f1.string ~ '^nuclear' AND
f1.id = article.oid )   AND  (f2.string ~ '^winter' AND f2.id =
article.oid )   ) ;
NOTICE:  QUERY PLAN:

Merge Join  (cost=1476541.78..1492435.98 rows=77581 width=228)
  ->  Merge Join  (cost=740017.07..744846.55 rows=368824 width=224)
        ->  Sort  (cost=3492.36..3492.36 rows=17534 width=220)
              ->  Seq Scan on article  (cost=0.00..1067.34 rows=17534
width=220)
        ->  Sort  (cost=736524.71..736524.71 rows=368824 width=4)
              ->  Seq Scan on article_fti f2  (cost=0.00..693812.18
rows=368824 width=4)
  ->  Sort  (cost=736524.71..736524.71 rows=368824 width=4)
        ->  Seq Scan on article_fti f1  (cost=0.00..693812.18 rows=368824
width=4)

EXPLAIN
archive=> explain select * from article where text like '%nuclear%' and
text like '%winter%';
NOTICE:  QUERY PLAN:

Seq Scan on article  (cost=0.00..1155.01 rows=1 width=216)

EXPLAIN
archive=> \d article_fti
     Table "article_fti"
 Attribute | Type | Modifier
-----------+------+----------
 string    | text |
 id        | oid  |
Indices: article_fti_id_index,
         article_fti_string_index

archive=> \d article
                              Table "article"
     Attribute     |  Type   |                   Modifier
-------------------+---------+----------------------------------------------
 id                | integer | not null default nextval('article_ID'::text)
...
 text              | text    |
Indices: article_id_index,
         article_oid_index,
         article_type_index

archive=>

I'm befuddled.




In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2002-11-27 03:13:51
Subject: Re: [HACKERS] Realtime VACUUM, was: performance of insert/delete/update
Previous:From: Hannu KrosingDate: 2002-11-26 21:33:14
Subject: Re: Full Text Index disk space requirements

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group