Re: extensible enum types

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.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