Обсуждение: Using In Clause For a Large Text Matching Query

Hello all, my first post to the pgsql mailing list!

There may be a completely better way to do this; if so please help point 
me in the right direction!

What I'm trying to do is run a query to partially match 300+ text fields 
to a PG table of about 200,000 rows. The idea is to pull out a portion 
of text from the original text fields and do a partial text match on 
anything in my PG table containing that substring.

I liked the idea of using a where IN(group) to do my comparisons, as in

select col1 from table1 where col1 in ('text1','text2')

however, this requires an exact string match. Is there any way to do a 
substring match inside of my IN group? Or can anyone think of a better 
way to do something like this?

Heres an example of something of how I'd like this to work:

Portion of 300 Original Text fields:
"brown cat"
"green dog"

2 rows of 200k+ Database table:
"brown kitty"
"green doggy"

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.

I hope this makes sense, but if anything sounds confusing please let me 
know, and I will be sure to clarify! Thanks for any help or direction 
anyone can provide!!

- Jason Farmer

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

> Well, there is also: <a 
> href="http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13377">
> 
> /expression/ /operator/ ANY (/array expression/)</a>.  So, if you have a way to preprocess you
> input text fields that you want matched 
> you could build a regex for each and feed them in an array to an '~ ANY' expression like so (or,
> use ~* for case 
> insensitive matching):
> 
> SELECT col1
> FROM table
> WHERE col1 ~ ANY (ARRAY['regex1', 'regex2', ...]);

Good point,  But don't forget to include the list in your response. :-)

Regards,

Richard Broersma Jr.

Ah, I do think that sounds about like what I want! Let me play with this 
one some, thanks so much!!

Richard Broersma Jr wrote:
>> Well, there is also: <a 
>> href="http://www.postgresql.org/docs/8.1/interactive/functions-comparisons.html#AEN13377">
>>
>> /expression/ /operator/ ANY (/array expression/)</a>.  So, if you have a way to preprocess you
>> input text fields that you want matched 
>> you could build a regex for each and feed them in an array to an '~ ANY' expression like so (or,
>> use ~* for case 
>> insensitive matching):
>>
>> SELECT col1
>> FROM table
>> WHERE col1 ~ ANY (ARRAY['regex1', 'regex2', ...]);
>>     
>
> Good point,  But don't forget to include the list in your response. :-)
>
> Regards,
>
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>