[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] enum for performance?
From:       Merlin Moncure <mmoncure () gmail ! com>
Date:       2009-06-18 15:19:33
Message-ID: b42b73150906180819i5e124bbdm455179b5c3c69780 () mail ! gmail ! com
[Download RAW message or body]

On Wed, Jun 17, 2009 at 6:06 PM, Whit Armstrong<armstrong.whit@gmail.com> wrote:
> I have a column which only has six states or values.
>
> Is there a size advantage to using an enum for this data type?
> Currently I have it defined as a character(1).
>
> This table has about 600 million rows, so it could wind up making a
> difference in total size.

Here is what enums get you:
*) You can skip a join to a detail table if one char is not enough to
sufficiently describe the value to the user.
*) If you need to order by the whats contained in the enum, the gains
can be tremendous because it can be inlined in the index:

create table bigtable
(
  company_id bigint,
  someval some_enum_t,
  sometime timestamptz,
);

create index bigindex on bigtable(company_id, someval, sometime);

select * from bigtable order by 1,2,3 limit 50;
-- or
select * from bigtable where company_id = 12345 order by 2,3;

The disadvantage with enums is flexibility.  Sometimes the performance
doesn't matter or you need that detail table anyways for other
reasons.

Also, if you use "char" vs char(1), you shave a byte and a tiny bit of speed.

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]