References: <1536339842382-0.post@n3.nabble.com>
User-agent: mu4e 0.9.18; emacs 26.1
From: Tim Cross <theophilusx@gmail.com>
To: padusuma <venkata.adusumalli@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of INSERT into temporary tables using psqlODBC driver
In-reply-to: <1536339842382-0.post@n3.nabble.com>
Date: Sat, 08 Sep 2018 10:05:10 +1000
Message-ID: <8736uk6eh5.fsf@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk


padusuma <venkata.adusumalli@gmail.com> 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