Thread: What's the best type of index for an ENUM column?
I have a column in a table that's an ENUM type. What's the best type of index for this? It seems if I use a btree index, that's more optimized for doing greater than or less than comparisons which don't apply to an enum. I will only be doing equals comparisons on these columns. Thanks! Mike
Mike Christensen <mike@kitchenpc.com> writes: > I have a column in a table that's an ENUM type. What's the best type > of index for this? It seems if I use a btree index, that's more > optimized for doing greater than or less than comparisons which don't > apply to an enum. I will only be doing equals comparisons on these > columns. Thanks! You aren't going to find that anything else beats out btree, I expect. If we had bitmap indexes, those might be superior; but we don't. regards, tom lane
Thanks, that's what I figured. Just to double check, an enum type is gonna be stored on the disk as an integer, correct? And all indexes will be based on the integer value of the enum? Postgres only wants you to believe it's a string when you look at the data. Mike On Mon, Jun 7, 2010 at 8:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Mike Christensen <mike@kitchenpc.com> writes: >> I have a column in a table that's an ENUM type. What's the best type >> of index for this? It seems if I use a btree index, that's more >> optimized for doing greater than or less than comparisons which don't >> apply to an enum. I will only be doing equals comparisons on these >> columns. Thanks! > > You aren't going to find that anything else beats out btree, I expect. > If we had bitmap indexes, those might be superior; but we don't. > > regards, tom lane >
Mike Christensen <mike@kitchenpc.com> writes: > Just to double check, an enum type is gonna be stored on the disk as > an integer, correct? And all indexes will be based on the integer > value of the enum? Well, strictly speaking it's an OID, but yeah. The label is only of interest for I/O. regards, tom lane