From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Jason Farmer <jfarmer@getloaded.com>
To: pgsql-sql@postgresql.org
Subject: Re: Using In Clause For a Large Text Matching Query
Date: Thu, 29 Jun 2006 13:49:27 -0700 (PDT)
Message-ID: <20060629204927.16465.qmail@web31802.mail.mud.yahoo.com> (raw)
In-Reply-To: <44A43172.3040202@getloaded.com>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, jfarmer@getloaded.com
Subject: Re: Using In Clause For a Large Text Matching Query
In-Reply-To: <20060629204927.16465.qmail@web31802.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox