padusuma <venkata.adusumalli@xxxxxxxxx> writes: > I am working on adding support for PostgreSQL database for our application. > In a lot of our use-cases, data is inserted into temporary tables using > INSERT INTO statements with bind parameters, and subsequently queries are > run by joining to these temp tables. Following is some of the data for these > INSERT statements: > > Table definition: CREATE TEMPORARY TABLE Table1( auid varchar(15) ) ON > COMMIT DELETE ROWS; > > SQL statement: INSERT INTO Table1 (uidcol) VALUES (:1); > > Time taken to insert 24428 rows: 10.077 sec > Time taken to insert 32512 rows: 16.026 sec > Time taken to insert 32512 rows: 15.821 sec > Time taken to insert 6107 rows: 1.514 sec > > I am looking for suggestions to improve the performance of these INSERT > statements into temporary tables. Database is located on a Linux VM and the > version is "PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) > 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit". The application is running on a > windows platform and connecting to the database using psqlODBC driver > version 10.03. > We are inserting large numbers (millions) of rows into a postgres database from a Javascript application and found using the COPY command was much, much faster than doing regular inserts (even with multi-insert commit). If you can do this using the driver you are using, that will give you the largest performance boost. -- Tim Cross
