Re: [PERFORM] enum for performance?
On Wed, Jun 17, 2009 at 6:06 PM, Whit Armstrongarmstrong.w...@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
[PERFORM] enum for performance?
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. Thanks, Whit -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] enum for performance?
Whit Armstrong armstrong.w...@gmail.com writes: 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). Nope. enums are always 4 bytes. char(1) is going to take 2 bytes (assuming those six values are simple ASCII characters), at least as of PG 8.3 or later. Depending on what the adjacent columns are, the enum might not actually cost you anything --- the difference might well disappear into alignment padding anyway. But it's not going to save. Another possibility is to look at the char (not char) type, which also stores single ASCII-only characters. That's just one byte. But again, it might well not save you anything, depending on alignment considerations. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance