Re: Inheritance planner CPU and memory usage change since 9.3.2

On 21 June 2015 at 05:27, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Jun 20, 2015 at 6:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I propose instead the attached patch, which operates by identifying which
>> of the append_rel_list entries actually contain subquery references, and
>> copying only those; the other ones are just linked into the child's
>> append_rel_list by reference, which is okay because they won't get
>> modified.
>
> Better than unpatched, definitely! Not sure how it compares to my patch.
>

I tested on my machine (optimised build, asserts off). With HEAD I got:

Q1: 8076ms
Q2: 7165ms
Q3: 4027ms
Q4: OOM (killed by kernel, used > 16GB RAM)
Q5: 4131ms

The machine only has 16GB of RAM and almost no swap, so it wasn't able to do Q4.

With Robert's patch:

Q1: 1121ms
Q2: 542ms
Q3: 498ms
Q4: 50763ms (used 3GB RAM)
Q5: 556ms

and with Tom's patch:

Q1: 2264ms
Q2: 3785ms
Q3: 507ms
Q4: 50851ms (used 3GB RAM)
Q5: 558ms

However, there's an obvious improvement that can be made to Tom's
patch -- having computed modifiableARIindexes, you may as well use it
in the innermost loop to only apply ChangeVarNodes() to those
AppendRelInfo's that can actually change, rather than having it trawl
through all the other ones that we know won't be touched.

With that improvement (attached), the timings become:

Q1: 1148ms
Q2: 547ms
Q3: 505ms
Q4: 51325ms
Q5: 544ms

i.e., basically the same as Robert's patch.

Regards,
Dean