[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    [GENERAL] Full Text Indexing and Syntax
From:       "flood" <bfd3000 () gmail ! com>
Date:       2006-02-28 21:54:45
Message-ID: 1141163685.567503.54420 () i39g2000cwa ! googlegroups ! com
[Download RAW message or body]

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 .00..30040.00 rowsP01 width=8)
   Join Filter: (lower("outer".keyword) ~ lower(('^'::text ||
"inner".article)))
   ->  Seq Scan on test1 t1  (cost=0.00..20.00 rows00 width6)
   ->  Materialize  (cost .00..30.00 rows00 width6)
         ->  Seq Scan on test2 t2  (cost=0.00..20.00 rows00
width6)


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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
[prev in list] [next in list] [prev in thread] [next in thread]