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
Date: Sat, 08 Sep 2018 10:05:10 +1000
Message-ID: <8736uk6eh5.fsf@gmail.com> (raw)
In-Reply-To: <1536339842382-0.post@n3.nabble.com>
References: <1536339842382-0.post@n3.nabble.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: theophilusx@gmail.com, venkata.adusumalli@gmail.com
Subject: Re: Performance of INSERT into temporary tables using psqlODBC driver
In-Reply-To: <8736uk6eh5.fsf@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox