Re: Typmod associated with multi-row VALUES constructs

From: Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: david(dot)g(dot)johnston(at)gmail(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Typmod associated with multi-row VALUES constructs
Date: 2016-12-13 10:06:00
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hello, it seems to me to work as expected.

At Thu, 08 Dec 2016 15:58:10 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote in <7083(dot)1481230690(at)sss(dot)pgh(dot)pa(dot)us>
> I've pushed the previous patch to HEAD. Attached is a proposed patch
> (against 9.6) that we could use for the back branches; it takes the
> brute force approach of just computing the correct value on-demand
> in the two functions at issue. The key question of course is whether
> this is acceptable from a performance standpoint. I did a simple test
> using a 1000-entry VALUES list:
> select count(a) from (
> values
> ('0'::varchar(3), '0'::varchar(4)),
> ('1'::varchar(3), '1'::varchar(4)),
> ('2'::varchar(3), '2'::varchar(4)),
> ('3'::varchar(3), '3'::varchar(4)),
> ('4'::varchar(3), '4'::varchar(4)),
> ...
> ('996'::varchar(3), '996'::varchar(4)),
> ('997'::varchar(3), '997'::varchar(4)),
> ('998'::varchar(3), '998'::varchar(4)),
> ('999'::varchar(3), '999'::varchar(4))
> ) v(a,b);
> Since all the rows do have the same typmod, this represents the worst
> case where we have to scan all the way to the end to confirm the typmod,
> and it has about as little overhead otherwise as I could think of doing.
> I ran it like this:
> pgbench -U postgres -n -c 1 -T 1000 -f bigvalues.sql regression
> and could not see any above-the-noise-level difference --- in fact,
> it seemed like it was faster *with* the patch, which is obviously
> impossible; I blame that on chance realignments of loops vs. cache
> line boundaries.
> So I think this is an okay candidate for back-patching. If anyone
> wants to do their own performance tests, please do.

For all the additional computation, I had the same result on
9.6. The patch accelerates the processing around the noise rate..

9.6 without the patch 103.2 tps
9.6 with the patch 108.3 tps

For the master branch,

master 102.9 tps
0b78106c^ 103.4 tps


Kyotaro Horiguchi
NTT Open Source Software Center

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro HORIGUCHI 2016-12-13 10:15:32 Re: Re: [sqlsmith] FailedAssertion("!(XLogCtl->Insert.exclusiveBackup)", File: "xlog.c", Line: 10200)
Previous Message Amit Langote 2016-12-13 08:45:41 Re: Declarative partitioning - another take