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