FAQ
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

Search Discussions

  • Josh Kupershmidt at Jun 4, 2011 at 1:15 pm

    On Wed, Jun 1, 2011 at 6:09 PM, Jean-Yves F. Barbier 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
  • Jean-Yves F. Barbier at Jun 4, 2011 at 4:15 pm
    On Fri, 3 Jun 2011 23:01:10 -0400, Josh Kupershmidt 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
    ...

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 1, '11 at 10:09p
activeJun 4, '11 at 4:15p
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase