Re: Small performance regression in 9.2 has a big impact

Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> writes:
> On Tue, Nov 25, 2014 at 1:58 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>> I don't know why this regressed between those versions, but looking at the
>> functions, there's some low-hanging fruit:
>>
>> 1. tree_ancestor_keys() could use UNION ALL instead of UNION. (I believe
>> duplicates are expected here, although I'm not 100% sure).
>>
>> 2. tree_ancestor_keys() calculates tree_level($1) every time it recurses.
>> Would be cheaper to calculate once, and pass it as argument.

> Wow that made a huge difference. About a 50% increase across the
> board. Sadly, 9.2 is still way behind 8.4 (see Tom's email)

Switching from UNION to UNION ALL would dodge the varbit hash-opclass
caching issue, I think. But there's still something else going on.

regards, tom lane