Erik,
Thanks for your answers, actually this is a workable solution because my
data does not get updated so frequently (every 24 hours). The problem
is that I would like a more advanced version of this, there must be
something I can do, I am going to try what Hubert Despez explained in
his articles.
Thanks :)
On Feb 11, 2008, at 9:37 AM, Mario Lopez wrote:
Hi guys :-), I am working on a personal project in which I am trying
to make sense on a huge (at least for me) amount of data. I have
approximately 150 million rows of unique words (they are not exactly
words it is just for explaining the situation).
The table I am inserting this is a quite simple table, something like
this:
CREATE TABLE "public"."names" (
"id" SERIAL,
"name" VARCHAR(255)
) WITHOUT OIDS;
It is a requirement that I can make searches on the varchar with
queries that look the following way:
SELECT * FROM names WHERE name LIKE ‘keyword%’
Or
SELECT * FROM names WHERE name LIKE ‘%keyword%’
I optimized the first type of queries making partitions with every
letter that a name can begin with:
CREATE TABLE "public"."names_a" (
CONSTRAINT "names_a_check" CHECK (("name")::text ~~ 'a%'::text)
) INHERITS ("public"."names")
WITHOUT OIDS;
The problem arises with the second type of queries, where there are
no possible partitions and that the search keywords are not known, I
have tried making indexes on the letter it ends with, or indexes that
specify that it contains the letter specified but none of them work
the planifier only make sequential scans over the table.
For the moment the quickest scan I have being able to make is using
grep!!, surprisingly enough grep searches on an average of 20 seconds
a whole plain text file of 2 GB one name per line and PostgreSQL on
the fist type of queries takes like 50 seconds while the second type
of queries con take up to two minutes which is completely
unacceptable for an online search engine that has to attend a user
querying this information.
How does this big search engines let’s say Google make this up? I am
amazed of the quickness on searching this amount of information in so
little time. Any approach I could take? I am open minded so anything
is acceptable not necessarily only PostgreSQL based solutions
(although I would prefer it). By the way Textual Search in PostgreSQL
is discarded because what I am looking at are not names that can be
decomposed on lexems, let's say that this varchar is composed of
random garbage.
Actually, a friend of mine actually did exactly what you've tried:
grep. He had a cron job that would update the txt file from the
table's data every five minutes and then his app would shell out to
run those kinds of queries. Of course, with a setup like that your
results can be a little out of date (the period between runs of the
cron job) but, if you can deal with that, that's actually a pretty
simple solution that doesn't take too much setup.
Erik Jones
DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
