Skip site navigation (1) Skip section navigation (2)

Re: BYTEA index

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 (view raw or flat)
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

In response to

  • BYTEA index at 2011-06-01 22:09:47 from Jean-Yves F. Barbier

Responses

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2011-06-04 13:09:40
Subject: Re: BYTEA index
Previous:From: Alan HodgsonDate: 2011-06-03 17:27:13
Subject: Re: - Upgrade advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group