Re: Using bytea field...

On Tue, Mar 8, 2011 at 8:00 PM, David Johnston <polobo(at)yahoo(dot)com> wrote:
> You could avoid the synchronization issues by putting the hash in an index...in theory...I'd wait for someone else to opine on that particular option.

Yes, a functional index on MD5(your_bytea_column) will work and is the
way to go for equality comparisons on bytea values. You can use the
built-in MD5() function or one of the various hash functions in
pgcrypto. You can also save some index size by only storing the
bytea-encoded md5 result in the index, something like:

CREATE INDEX bigcol_idx ON foo (decode(MD5(bigcol), 'hex'));

And then run formulate your queries similarly so they use the index:

test=# EXPLAIN ANALYZE SELECT * FROM foo
WHERE (decode(md5(bigcol), 'hex')) = (decode(md5('4'), 'hex'));

QUERY PLAN

-------------------------------------------------------------------------------
---------------------------------
Index Scan using bigcol_idx on foo (cost=0.00..8.28 rows=1 width=4) (actual t
ime=0.032..0.034 rows=1 loops=1)
Index Cond: (decode(md5(bigcol), 'hex'::text) = '\xa87ff679a2f3e71d9181a67b7
542122c'::bytea)
Total runtime: 0.095 ms
(3 rows)

Josh