From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Whit Armstrong <armstrong(dot)whit(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: enum for performance? |
Date: | 2009-06-18 15:19:33 |
Message-ID: | b42b73150906180819i5e124bbdm455179b5c3c69780@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jun 17, 2009 at 6:06 PM, Whit Armstrong<armstrong(dot)whit(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Cox | 2009-06-18 17:06:35 | Re: very slow selects on a small table |
Previous Message | Alberto Dalmaso | 2009-06-18 08:02:08 | Re: performance with query |