DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=LLsdyuIwB9gE/Q/Zx5opO/guOsubVSO80kKqh+gPaT8hUmy5nvitFlyvevr/jx7SVSPq+XDJfxLrWt8pG938cqSdKVHEDcwMWR9/xG69ewayeOb7cdg17AJ6rv1rG71szifnmKQ4uT58pxAJ7Mf5b4w8fQcDZqccx+nGO49fDm8=
	;
Message-ID: <20060629204927.16465.qmail@web31802.mail.mud.yahoo.com>
Date: Thu, 29 Jun 2006 13:49:27 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Using In Clause For a Large Text Matching Query
To: Jason Farmer <jfarmer@getloaded.com>, pgsql-sql@postgresql.org
In-Reply-To: <44A43172.3040202@getloaded.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> We can assume a certain portion of the text is included in the DB table, 
> so I want to be able to do a substring match on "brown" and "green" and 
> in this case return both "brown kitty", and "green doggy". However the 
> problem is, if I run the query on each of my 300 rows to scan 200,000 
> rows in my DB is entirely too slow. So I was hoping to use the IN clause 
> to create an IN group of about 300 items to scan the DB once.

You can probably do it.  However, you will have to pick a substring from your text field to
compare against.  In this case you seem to be choosing the first word, i.e. "brown" and "green".

so maybe:

select t1.col1
from
     table1 as t1,
    (
       select distinct split_part( tblFileDump.Col1, ' ', 1 ) as samplecol
       from tblFileDump
    ) as fd1

where t1.col1 like '%' || fd1.samplecol || '%'
;

This is just an idea.  I've never used split_part or developed a sudo join this way.  But it may
work provided you and jump your text files into a temp table.

Notice:
http://www.postgresql.org/docs/8.1/interactive/functions-string.html
for the syntax for split_part().

Regards,

Richard Broersma Jr.