Thread: BYTEA index

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?

JY
--
The number of arguments is unimportant unless some of them are correct.
        -- Ralph Hartley

On Wed, Jun 1, 2011 at 6:09 PM, Jean-Yves F. Barbier <12ukwn@gmail.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

On Fri, 3 Jun 2011 23:01:10 -0400, Josh Kupershmidt <schmiddy@gmail.com> wrote:

...
> 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).

Ok that what I tought; but I'm gonna stick to BYTEA NULL values indexation
only 'cos this table is heavy already (goal is just to check whether an
invoice's PDF has been created or not.)

Thanks Josh
...