Index on TEXT versus CHAR(32)... fast exact TEXT matching

From: Jon Lapham <lapham(at)jandr(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Index on TEXT versus CHAR(32)... fast exact TEXT matching
Date: 2004-09-04 13:04:56
Message-ID: 4139BD78.40903@jandr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a table that stores TEXT information. I need query this table to
find *exact* matches to the TEXT... no regular expressions, no LIKE
queries, etc. The TEXT could be from 1 to 10000+ characters in length,
quite variable. If it matters, the TEXT may contain UNICODE characters...

Example:
CREATE TABLE a (id SERIAL, thetext TEXT);
SELECT id FROM a WHERE thetext='Some other text';

One way I thought to optimize this process would be to store an MD5 hash
of the "thetext" column and query on that:

CREATE TABLE a (id SERIAL, thetext TEXT, thetext_md5 CHAR(32));
SELECT id FROM a WHERE thetext_m5d=md5('Some other text');

Now, obviously I would want to build an INDEX on either "thetext" or
"thetext_md5", depending on which way I decide to make the table.

My question is, what is the absolute fastest way to find the exact match
to a TEXT column? Any amount of pre-processing is fine (such as
calculating the MD5's of all the TEXT tuples), but the query must be
extremely fast.

Has anyone compared (theoretical or practical) the performance of
querying a TEXT-based INDEX versus a CHAR(32)-based INDEX? Is my MD5
idea a waste of time? Is there something better than MD5? Would it be
better to store the "fingerprint" of the TEXT as an integer somehow, so
that the INDEX could be based on a INT* column?

Thanks for any help!

-Jon

--
-**-*-*---*-*---*-*---*-----*-*-----*---*-*---*-----*-----*-*-----*---
Jon Lapham <lapham(at)jandr(dot)org> Rio de Janeiro, Brasil
Personal: http://www.jandr.org/
***-*--*----*-------*------------*--------------------*---------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Russ Brown 2004-09-04 16:05:32 psql leaking?
Previous Message Phil Latio 2004-09-04 13:00:09 Re: Where can I get pgsqlodbc now gborg is down?