From: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
---|---|
To: | "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: BYTEA index |
Date: | 2011-06-04 03:01:10 |
Message-ID: | BANLkTik2fHmMUR=-ULp2nNPC1rB2nEJ=RA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Yves F. Barbier | 2011-06-04 13:09:40 | Re: BYTEA index |
Previous Message | Alan Hodgson | 2011-06-03 17:27:13 | Re: - Upgrade advice |