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:
AFAIK, you only need to add an index on "name" to be able to speed up the first kind of queries.
Have a look at B-Tree description in [1].
- Jonathan
[1] : http://www.postgresql.org/docs/8.3/interactive/indexes-types.html
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
