Re: extensible enum types

Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>
>> Another thought: could we add a column to pg_type with a flag that's
>> true if the oids are in sort order? Then the comparison routines could
>> just look that up in the type cache and if it's true (as it often will
>> be) just return the oid comparison.
>>
>
> Well, having to do a cache lookup already makes it a couple orders of
> magnitude more expensive than an OID comparison. However, it's hard to
> say how much that matters in terms of total application performance.
> We really could do with a bit of performance testing here ...
>
>
>

I have done some. The performance hit is fairly horrible. Adding cache
lookups for the enum rows to the comarison routines made a REINDEX on a
1m row table where the index is on an enum column (the enum has 500
randomly ordered labels) jump from around 10s to around 70s. I think
that probably rules out doing anything like this for the existing enum
types. I think the most we can reasonably do there is to allow adding a
label to the end of the enum list. I'm fairly resistant to doing
something which will have a major performance impact, as I know there
are users who are relying on enums for performce reasons. I'm also
fairly resistant to doing things which will require table rewriting.

So the question then is: do we want to allow lots of flexibility for
positioning new labels with significant degradation in comparison
performace for a new enum variant, or have a new variant with some
restrictions which probably won't impact most users but would have
equivalent performance to the current enum family, or do nothing?

cheers

andrew