Inheritance planner CPU and memory usage change since 9.3.2

From: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Inheritance planner CPU and memory usage change since 9.3.2
Date: 2015-06-17 13:32:58
Message-ID: CAEepm=1-3vRARS-jWiH-VZShXGSFBZzw=-WhE2Ui4msZxDWLyw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

We saw a rather extreme performance problem in a cluster upgraded from
9.1 to 9.3. It uses a largish number of child tables (partitions) and
many columns. Planning a simple UPDATE via the base table started
using a very large amount of memory and CPU time.

My colleague Rushabh Lathia tracked the performance change down to
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c03ad5602f529787968fa3201b35c119bbc6d782
.

The call to copyObject in the loop introduced here seems to be
problematic (copying append_rel_list for every element in
append_rel_list unconditionally), though we're still trying to figure
it out. Attached is a simple repro script, with variables to tweak.

Quite a few others have posted about this sort of thing and been
politely reminded of the 100 table caveat [1][2] which is fair enough,
but the situations seems to have got dramatically worse for some users
after an upgrade.

[1] http://www.postgresql.org/message-id/8c9acaa.1f453.14c0da0402f.Coremail.chjischj@163.com
[2] http://www.postgresql.org/message-id/flat/20141107185824(dot)2513(dot)53433(at)wrigleys(dot)postgresql(dot)org#20141107185824(dot)2513(dot)53433@wrigleys.postgresql.org

--
Thomas Munro
http://www.enterprisedb.com

Attachment Content-Type Size
repro-planner-explosion.sh application/x-sh 631 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2015-06-17 13:56:36 Re: Inheritance planner CPU and memory usage change since 9.3.2
Previous Message Noah Misch 2015-06-17 12:24:13 Re: "could not adopt C locale" failure at startup on Windows