| From: | "flood" <bfd3000(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | Full Text Indexing and Syntax | 
| Date: | 2006-02-28 21:54:45 | 
| Message-ID: | 1141163685.567503.54420@i39g2000cwa.googlegroups.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Hi folks, I am having some trouble with this query that should be using
FTI.  There are 2 tables, one with a list of keywords and the other
containing a body of articles.
I am trying to get a query to return the IDs of each keyword with the
ID of each article that contains that keyword.
So the 2 tables are:
test1:
 ->id
 ->keyword
test2:
 ->id
 ->article
Unfortunately I can not seem to get my query to use PG's full text
indexing, it keeps doing a seq scan:
EXPLAIN
SELECT t1.id, t2.id
FROM test1 t1, test2 t2
WHERE lower( t1.keyword ) ~ ( lower ( '^' || t2.article ));
---------------------------------------------------------------------------------
 Nested Loop  (cost=20.00..30040.00 rows=5001 width=8)
   Join Filter: (lower("outer".keyword) ~ lower(('^'::text ||
"inner".article)))
   ->  Seq Scan on test1 t1  (cost=0.00..20.00 rows=1000 width=36)
   ->  Materialize  (cost=20.00..30.00 rows=1000 width=36)
         ->  Seq Scan on test2 t2  (cost=0.00..20.00 rows=1000
width=36)
Could someone explain how I can restructure this SQL to use the index,
or perhaps suggest a workaround?  
I am using PG 7.4.7 on Debian 3.1.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Karen Ploski | 2006-02-28 22:14:13 | Questions about large objects and the WAL | 
| Previous Message | Martijn van Oosterhout | 2006-02-28 21:23:54 | Re: Size comparison between a Composite type and an |