Re: making update/delete of inheritance trees scale better

On Wed, Mar 31, 2021 at 7:13 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
> > However, I then tried a partitioned equivalent of the 6-column case
> > (script also attached), and it looks like
> > 6 columns 16551 19097 15637 18201
> > which is really noticeably worse, 16% or so.
>
> ... and on the third hand, that might just be some weird compiler-
> and platform-specific artifact.
>
> Using the exact same compiler (RHEL8's gcc 8.3.1) on a different
> x86_64 machine, I measure the same case as about 7% slowdown not
> 16%. That's still not great, but it calls the original measurement
> into question, for sure.
>
> Using Apple's clang 12.0.0 on an M1 mini, the patch actually clocks
> in a couple percent *faster* than HEAD, for both the partitioned and
> unpartitioned 6-column test cases.
>
> So I'm not sure what to make of these results, but my level of concern
> is less than it was earlier today. I might've just gotten trapped by
> the usual bugaboo of micro-benchmarking, ie putting too much stock in
> only one test case.

FWIW, I ran the scripts you shared and see the following (median of 3
runs) times in ms for the UPDATE in each script:

heikki6.sql

master: 19139 (jit=off) 18404 (jit=on)
patched: 20202 (jit=off) 19290 (jit=on)

hekki10.sql

master: 21686 (jit=off) 21435 (jit=on)
patched: 20953 (jit=off) 20161 (jit=on)

Patch shows a win for 10 columns here.

part6.sql

master: 20321 (jit=off) 19580 (jit=on)
patched: 22661 (jit=off) 21636 (jit=on)

I wrote part10.sql and ran that too, with these results:

master: 22280 (jit=off) 21876 (jit=on)
patched: 23466 (jit=off) 22905 (jit=on)

The partitioned case slowdown is roughly 10% with 6 columns, 5% with
10. I would agree that that's not too bad for a worse-case test case,
nor something we couldn't optimize. I have yet to look closely at
where the problem lies though.

--
Amit Langote
EDB: http://www.enterprisedb.com