On Sun, Jun 13, 2021 at 6:10 AM Alvaro Herrera <alvhe...@alvh.no-ip.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.
On Sun, Jun 13, 2021 at 7:25 PM Tomas Vondra
<tomas.von...@enterprisedb.com> wrote:
> Good idea. I gave that a try, creating a table with 1500 columns and
> inserting 50 rows (so 75k parameters). See the attached patch.
Thanks for the patch. I also prepared a patch, just sharing. I'm okay
if it's ignored.
With Regards,
Bharath Rupireddy.
From c97cc8385a8e0ee5e5b0f3b4532a7107b1a183f6 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sun, 13 Jun 2021 02:07:20 -0700
Subject: [PATCH v1] enhance batch insert test case
---
.../postgres_fdw/expected/postgres_fdw.out | 23 ++++++++++++++-----
contrib/postgres_fdw/sql/postgres_fdw.sql | 21 +++++++++++++----
2 files changed, 33 insertions(+), 11 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 1fb26639fc..cb8a8c8d5c 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -9683,16 +9683,27 @@ SELECT COUNT(*) FROM ftable;
TRUNCATE batch_table;
DROP FOREIGN TABLE ftable;
-- try if large batches exceed max number of bind parameters
-CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' );
-INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i;
-SELECT COUNT(*) FROM ftable;
+DO LANGUAGE plpgsql
+$$
+DECLARE
+ l_tbl text := 'CREATE TABLE local_tbl_b('|| string_agg('c' || i || ' int', ',') || ');'
+ FROM generate_series(1,1600) As i;
+ f_tbl text := 'CREATE FOREIGN TABLE foreign_tbl_b(' || string_agg('c' || i || ' int', ',') || E') SERVER loopback OPTIONS (table_name \'local_tbl_b\', batch_size \'100000\');'
+ FROM generate_series(1,1600) As i;
+BEGIN
+ EXECUTE l_tbl;
+ EXECUTE f_tbl;
+END;
+$$;
+INSERT INTO foreign_tbl_b SELECT * FROM generate_series(1, 41) i;
+SELECT COUNT(*) FROM foreign_tbl_b;
count
-------
- 70000
+ 41
(1 row)
-TRUNCATE batch_table;
-DROP FOREIGN TABLE ftable;
+DROP FOREIGN TABLE foreign_tbl_b;
+DROP TABLE local_tbl_b;
-- Disable batch insert
CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '1' );
EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO ftable VALUES (1), (2);
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8cb2148f1f..1be80e07b9 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3027,11 +3027,22 @@ TRUNCATE batch_table;
DROP FOREIGN TABLE ftable;
-- try if large batches exceed max number of bind parameters
-CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '100000' );
-INSERT INTO ftable SELECT * FROM generate_series(1, 70000) i;
-SELECT COUNT(*) FROM ftable;
-TRUNCATE batch_table;
-DROP FOREIGN TABLE ftable;
+DO LANGUAGE plpgsql
+$$
+DECLARE
+ l_tbl text := 'CREATE TABLE local_tbl_b('|| string_agg('c' || i || ' int', ',') || ');'
+ FROM generate_series(1,1600) As i;
+ f_tbl text := 'CREATE FOREIGN TABLE foreign_tbl_b(' || string_agg('c' || i || ' int', ',') || E') SERVER loopback OPTIONS (table_name \'local_tbl_b\', batch_size \'100000\');'
+ FROM generate_series(1,1600) As i;
+BEGIN
+ EXECUTE l_tbl;
+ EXECUTE f_tbl;
+END;
+$$;
+INSERT INTO foreign_tbl_b SELECT * FROM generate_series(1, 41) i;
+SELECT COUNT(*) FROM foreign_tbl_b;
+DROP FOREIGN TABLE foreign_tbl_b;
+DROP TABLE local_tbl_b;
-- Disable batch insert
CREATE FOREIGN TABLE ftable ( x int ) SERVER loopback OPTIONS ( table_name 'batch_table', batch_size '1' );
--
2.25.1
