Postgres Pro
Downloads
Home   >   mailing lists

Thread: Full Text Indexing and Syntax

Full Text Indexing and Syntax

From
"flood"
Date:
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.

Re: Full Text Indexing and Syntax

From
Michael Glaesemann
Date:
On Mar 1, 2006, at 6:54 , flood wrote:

> 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 ));

It doesn't appear that you're using tsearch2. PostgreSQL does not
include full text search in the basic installation. Have you
installed tsearch2?

Michael Glaesemann
grzm myrealbox com

Re: Full Text Indexing and Syntax

From
"Joshua D. Drake"
Date:
>>
>> EXPLAIN
>> SELECT t1.id, t2.id
>> FROM test1 t1, test2 t2
>> WHERE lower( t1.keyword ) ~ ( lower ( '^' || t2.article ));
>
> It doesn't appear that you're using tsearch2. PostgreSQL does not
> include full text search in the basic installation. Have you installed
> tsearch2?
Which is included in the PostgreSQL contrib....

Joshua D. Drake

>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: PLphp, PLperl - http://www.commandprompt.com/