Re: WIP: extensible enums

On 10/24/2010 08:12 PM, Tom Lane wrote:
> OK, I did some timing consisting of building a btree index with
> maintenance_work_mem set reasonably high.  This is on a debug-enabled
> build, so it's not representative of production performance, but it will
> do for seeing what we're doing to enum comparison performance.  Here's
> what I tried:
>
> Stock 9.0.1            24.9 sec
>
> patch, all OIDs even        25.2 sec    (~ 1% hit)
>
> patch, half of OIDs odd        27.2 sec    (~ 9% hit)
>
> same, bitmapset forced null    64.9 sec    (~ 160% hit)
>
> (Note that the noise level in these measurements is about 1%;
> I'm not entirely convinced that the all-even case is really measurably
> slower than 9.0.)

Yeah, that was my conclusion. I tested with debug/cassert turned off, 
but my results were similar.

> The "half of OIDs odd" case is what you'd get for a binary upgrade
> from a 9.0 database.  The last case shows what happens if the
> intermediate bitmapset-test optimization is disabled, forcing all
> comparisons to do binary searches in the sorted-by-OID array
> (except for the one-quarter of cases where both OIDs are even
> by chance).  It's pretty grim but it represents a worst case that
> you'd be very unlikely to hit in practice.
>
> This shows that the bitmapset optimization really is quite effective,
> at least for cases where all the enum labels are sorted by OID after
> all.  That motivated me to change the bitmapset setup code to what's
> attached.  This is potentially a little slower at initializing the
> cache, but it makes up for that by still marking most enum members
> as sorted even when a few out-of-order members have been inserted.
> The key point is that an out-of-order member in the middle of the
> array doesn't prevent us from considering following members as
> properly sorted, as long as they are correctly ordered with respect to
> the other properly-sorted members.


That's nice. It's a tradeoff though. Bumping up the cost of setting up 
the cache won't have much effect on a creating a large index, but could 
affect to performance of retail comparisons significantly. But this is 
probably worth it. You'd have to work hard to create the perverse case 
that could result in seriously worse cache setup cost.

> With this approach we can honestly say that inserting an out-of-order
> enum value doesn't impact comparison performance for pre-existing
> enum members, only for comparisons involving the out-of-order value
> itself; even when the existing members were binary-upgraded and thus
> weren't all even.  I think that's a worthwhile advantage.

Yeah, that's nice.

> IMHO this level of performance is good enough.  Anyone unhappy?

No, seems good.

cheers

andrew