Re: ALTER TABLE ADD COLUMN fast default

On Tue, Mar 6, 2018 at 8:15 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 6 March 2018 at 22:40, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> Okay, it looks like the patch should disable physical tlists when
>> there's a missing column the same way as we do for dropped columns.
>> Patch attached.
>
> Please disregard the previous patch in favour of the attached.
>

OK, nice work, thanks. We're making good progress. Two of the cases I
was testing have gone from worse than master to better than master.
Here are the results I got, all against Tomas' 100-col 64-row table.
using pgbench -T 100:

select sum(c1000) from t;
fastdef tps = 4724.988619
master tps = 1590.843085
============
select c1000 from t;
fastdef tps = 5093.667203
master tps = 2437.613368
============
select sum(c1000) from (select c1000 from t offset 0) x;
fastdef tps = 3315.900091
master tps = 2067.574581
============
select * from t;
fastdef tps = 107.145811
master tps = 150.207957
============
select sum(c1), count(c500), avg(c1000) from t;
fastdef tps = 2304.636410
master tps = 1409.791975
============
select sum(c10) from t;
fastdef tps = 4332.625917
master tps = 2208.757119

"select * from t" used to be about a wash, but with this patch it's
got worse. The last two queries were worse and are now better, so
that's a win. I'm going to do a test to see if I can find the
break-even point between the second query and the fourth. If it turns
out to be at quite a large number of columns selected then I think it
might be something we can live with.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services