Re: BYTEA index

On Wed, Jun 1, 2011 at 6:09 PM, Jean-Yves F. Barbier <12ukwn(at)gmail(dot)com> wrote:
> Hi list,
>
> I noticed that creating an index against a BYTEA is possible; shall I assume I
> must index NULL value and exclude others, otherwise they'll be replicated into
> the index file?

Well, you *could* create a partial index which only covered the NULL
values, if you think such an index would actually be useful (i.e. you
have a lot of queries looking for NULL values in this table).

It's possible, but probably not a good idea, to index non-null bytea
values: performance will be bad for several reasons, and if you have
large bytea values you're going to run into an error message like:

ERROR: index row size 3024 exceeds maximum 2712 for index "bytea_idx"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full
text indexing.

The HINT: above about creating a functional index on the MD5() of your
bytea value is usually what's useful for bytea indexing. (You could
probably get around the above error by using a hash index method
instead of btree, but MD5() is really the way to go).

Josh