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
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).
In response to
- BYTEA index at 2011-06-01 22:09:47 from Jean-Yves F. Barbier
pgsql-novice by date
|Next:||From: Jean-Yves F. Barbier||Date: 2011-06-04 13:09:40|
|Subject: Re: BYTEA index|
|Previous:||From: Alan Hodgson||Date: 2011-06-03 17:27:13|
|Subject: Re: - Upgrade advice|