On 10/19/2010 12:53 PM, Andrew Dunstan wrote:
>
>
> On 10/19/2010 12:21 AM, Andrew Dunstan wrote:
>>
>>
>> On 10/18/2010 10:52 AM, Tom Lane wrote:
>>> We could possibly deal with enum types that follow the existing
>>> convention if we made the cache entry hold a list of all the original,
>>> known-to-be-sorted OIDs. (This could be reasonably compact and
>>> cheap to
>>> probe if it were represented as a starting OID and a Bitmapset of delta
>>> values, since we can assume that the initial set of OIDs is pretty
>>> close
>>> together.) But we have to have that cache entry, and we have to
>>> consult
>>> it on every single comparison, so it's definitely going to be slower
>>> than before.
>>>
>>> So I'm thinking the comparison procedure goes like this:
>>>
>>> 1. Both OIDs even?
>>> If so, just compare them numerically, and we're done.
>>>
>>> 2. Lookup cache entry for enum type.
>>>
>>> 3. Both OIDs in list of known-sorted OIDs?
>>> If so, just compare them numerically, and we're done.
>>>
>>> 4. Search the part of the cache entry that lists sort positions.
>>> If not both present, refresh the cache entry.
>>> If still not present, throw error.
>>>
>>> 5. Compare by sort positions.
>>>
>>> Step 4 is the slowest part but would be avoided in most cases.
>>> However, step 2 is none too speedy either, and would usually
>>> be required when dealing with pre-existing enums.
>>
>> OK, I've made adjustments that I think do what you're suggesting.
>>
>>
>
> I've discovered and fixed a couple more bugs in this. I have one or
> two more things to fix and then I'll send a new patch.
>
> Meanwhile, I've been testing a database that was upgraded from 9.0,
> so it has a lot of odd-numbered Oids. It's not really clear from
> performance testing that the bitmap is a huge win, or even a win at
> all. (Of course, my implementation might suck too.) I'll continue
> testing.
>
>
Well a bit more testing shows some benefit. I've sorted out a few kinks,
so this seems to work. In particular, with the above tables, the version
imported from 9.0 can create have an index created in about the same
time as on the fresh table (identical data, but all even numbered Oids).
Of course, with lots of odd numbered Oids, if a label gets added the
imported version will degrade in performance much more quickly.
The test timed is:
do $$ begin for i in 1 .. 20 loop drop index if exists idx1;
create index idx1 on mydata(label); end loop; end; $$;
Latest patch attached.
cheers
andrew