Re: ALTER TABLE ADD COLUMN fast default

On 02/16/2018 10:46 PM, Andrew Dunstan wrote:
> On Tue, Feb 13, 2018 at 6:28 PM, Andrew Dunstan
> <andrew(dot)dunstan(at)2ndquadrant(dot)com> wrote:
>> On Fri, Feb 9, 2018 at 3:54 PM, Andrew Dunstan
>> <andrew(dot)dunstan(at)2ndquadrant(dot)com> wrote:
>>> On Mon, Feb 5, 2018 at 7:49 AM, Andrew Dunstan
>>> <andrew(dot)dunstan(at)2ndquadrant(dot)com> wrote:
>>>> On Mon, Feb 5, 2018 at 7:19 AM, Thomas Munro
>>>> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>>>>> On Fri, Jan 26, 2018 at 1:23 PM, Andrew Dunstan
>>>>> <andrew(dot)dunstan(at)2ndquadrant(dot)com> wrote:
>>>>>> Yeah, thanks. revised patch attached
>>>>>
>>>>> FYI the identity regression test started failing recently with this
>>>>> patch applied (maybe due to commit
>>>>> 533c5d8bddf0feb1785b3da17c0d17feeaac76d8?)
>>>>>
>>>>
>>>> Thanks. Probably the same bug Tomas Vondra found a few days ago. I'm on it.
>>>>
>>>
>>>
>>> Here's a version that fixes the above issue and also the issue with
>>> VACUUM that Tomas Vondra reported. I'm still working on the issue with
>>> aggregates that Tomas also reported.
>>>
>>
>> This version fixes that issue. Thanks to Tomas for his help in finding
>> where the problem was. There are now no outstanding issues that I'm
>> aware of.
>>
>
>
> The attached version largely fixes with the performance degradation
> that Tomas Vondra reported, replacing an O(N^2) piece of code in
> slot_getmissingattrs() by one that is O(N).
>

Seems fine to me - for comparison, numbers for master and v9/v10 patch
versions look like this:

create.sql create-alter.sql
-----------------------------------------
master 1120 1320
v9 1100 50
v10 1130 1370

This particular machine has a laptop-class CPU, so the timings are
somewhat noisy - which explains the small tps differences.

What I find interesting is that if I do VACUUM FULL after running the
SQL script, I get this:

create.sql create-alter.sql
-----------------------------------------
master 1120 1450
v9 1100 1320
v10 1100 1450

That is, the throughput for create-alter case jumps up by about 100 tps,
for some reason. Not sure why, though ...

Anyway, I consider the performance to be OK. But perhaps Andres could
comment on this too, as he requested the benchmarks.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services