On 6/13/21 2:40 AM, Alvaro Herrera 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.
>
Good idea. I gave that a try, creating a table with 1500 columns and
inserting 50 rows (so 75k parameters). See the attached patch.
While this cuts the runtime about in half (to ~30 minutes on my laptop),
that's probably not enough - it's still about ~6x longer than it used to
take. All these timings are with valgrind.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 8cb2148f1f..0c6bc240b7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3026,13 +3026,6 @@ 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;
-TRUNCATE batch_table;
-DROP FOREIGN TABLE ftable;
-
-- 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);
@@ -3041,6 +3034,46 @@ SELECT COUNT(*) FROM ftable;
DROP FOREIGN TABLE ftable;
DROP TABLE batch_table;
+
+CREATE OR REPLACE FUNCTION create_batch_tables(p_cnt int) RETURNS void AS $$
+DECLARE
+ v_sql_local text := '';
+ v_sql_foreign text := '';
+ v_i int;
+BEGIN
+
+ v_sql_local := 'CREATE TABLE batch_table (';
+ v_sql_foreign := 'CREATE FOREIGN TABLE ftable (';
+
+ FOR v_i IN 1 .. p_cnt LOOP
+
+ IF v_i > 1 THEN
+ v_sql_local := v_sql_local || ', col_' || v_i || ' int';
+ v_sql_foreign := v_sql_foreign || ', col_' || v_i || ' int';
+ ELSE
+ v_sql_local := v_sql_local || 'col_' || v_i || ' int';
+ v_sql_foreign := v_sql_foreign || 'col_' || v_i || ' int';
+ END IF;
+
+ END LOOP;
+
+ v_sql_local := v_sql_local || ')';
+ v_sql_foreign := v_sql_foreign || ') SERVER loopback OPTIONS (table_name ''batch_table'', batch_size ''100000'' )';
+
+ EXECUTE v_sql_local;
+ EXECUTE v_sql_foreign;
+
+END;
+$$ LANGUAGE plpgsql;
+
+-- try if large batches exceed max number of bind parameters
+SELECT create_batch_tables(1500);
+INSERT INTO ftable SELECT * FROM generate_series(1, 50) i;
+SELECT COUNT(*) FROM ftable;
+DROP TABLE batch_table;
+DROP FOREIGN TABLE ftable;
+DROP FUNCTION create_batch_tables(int);
+
-- Use partitioning
CREATE TABLE batch_table ( x int ) PARTITION BY HASH (x);
