Tom Lane wrote:
Andrew Dunstan <and...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
