Re: Fdw batch insert error out when set batch_size > 65535

On Sun, Jun 13, 2021 at 9:28 PM Tomas Vondra
<tomas(dot)vondra(at)enterprisedb(dot)com> wrote:
> On 6/13/21 5:25 PM, Bharath Rupireddy wrote:
> > On Sun, Jun 13, 2021 at 6:10 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> >>
> >> On 2021-Jun-12, Tomas Vondra wrote:
> >>
> >>> There's one caveat, though - for regular builds the slowdown is pretty
> >>> much eliminated. But with valgrind it's still considerably slower. For
> >>> postgres_fdw the "make check" used to take ~5 minutes for me, now it
> >>> takes >1h. And yes, this is entirely due to the new test case which is
> >>> generating / inserting 70k rows. So maybe the test case is not worth it
> >>> after all, and we should get rid of it.
> >>
> >> Hmm, what if the table is made 1600 columns wide -- would inserting 41
> >> rows be sufficient to trigger the problem case? If it does, maybe it
> >> would reduce the runtime for valgrind/cache-clobber animals enough that
> >> it's no longer a concern.
> >
> > Yeah, that's a good idea. PSA patch that creates the table of 1600
> > columns and inserts 41 rows into the foreign table. If the batch_size
> > adjustment fix isn't there, we will hit the error. On my dev system,
> > postgres_fdw contrib regression tests execution time: with and without
> > the attached patch 4.5 sec and 5.7 sec respectively.
> >
>
> But we're discussing cases with valgrind and/or CLOBBER_CACHE_ALWAYS.

Okay. Here are the readings on my dev system:
1) on master with the existing test case with inserting 70K rows:
4263200 ms (71.05 min)
2) with Tomas's patch with the test case modified with 1500 table
columns and 50 rows, (majority of the time ~30min it took in SELECT
create_batch_tables(1500); statement. I measured this time manually
looking at the start and end time of the statement - 6649312 ms (110.8
min)
3) with my patch with test case modified with 1600 table columns and
41 rows: 4003007 ms (66.71 min)
4) on master without the test case at all: 3770722 ms (62.84 min)

With Regards,
Bharath Rupireddy.