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
Show quoted text
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