Re: Inheritance planner CPU and memory usage change since 9.3.2

On Sun, Jun 21, 2015 at 5:45 AM, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> 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.

Cool. That sounds good.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company