pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAny way to speed up INSERT INTO
11+ messages / 5 participants
[nested] [flat]
* Any way to speed up INSERT INTO
@ 2022-03-04 18:31 aditya desai <admad123@gmail.com>
2022-03-04 18:38 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 11+ messages in thread
From: aditya desai @ 2022-03-04 18:31 UTC (permalink / raw)
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
One of the service layer app is inserting Millions of records in a table
but one row at a time. Although COPY is the fastest way to import a file in
a table. Application has a requirement of processing a row and inserting it
into a table. Is there any way this INSERT can be tuned by increasing
parameters? It is taking almost 10 hours for just 2.2 million rows in a
table. Table does not have any indexes or triggers.
Regards,
Aditya.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
@ 2022-03-04 18:38 ` Bruce Momjian <bruce@momjian.us>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2022-03-04 18:38 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Sat, Mar 5, 2022 at 12:01:52AM +0530, aditya desai wrote:
> Hi,
> One of the service layer app is inserting Millions of records in a table but
> one row at a time. Although COPY is the fastest way to import a file in a
> table. Application has a requirement of processing a row and inserting it into
> a table. Is there any way this INSERT can be tuned by increasing parameters? It
> is taking almost 10 hours for just 2.2 million rows in a table. Table does not
> have any indexes or triggers.
Well, sections 14.4 and 14.5 might help:
https://www.postgresql.org/docs/14/performance-tips.html
Your time seems very slow --- are the rows very wide?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:38 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
@ 2022-03-04 18:42 ` aditya desai <admad123@gmail.com>
2022-03-05 01:22 ` Re: Any way to speed up INSERT INTO Imre Samu <pella.samu@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: aditya desai @ 2022-03-04 18:42 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Bruce,
Correct rows are wider. One of the columns is text and one is bytea.
Regards,
Aditya.
On Sat, Mar 5, 2022 at 12:08 AM Bruce Momjian <bruce@momjian.us> wrote:
> On Sat, Mar 5, 2022 at 12:01:52AM +0530, aditya desai wrote:
> > Hi,
> > One of the service layer app is inserting Millions of records in a table
> but
> > one row at a time. Although COPY is the fastest way to import a file in a
> > table. Application has a requirement of processing a row and inserting
> it into
> > a table. Is there any way this INSERT can be tuned by increasing
> parameters? It
> > is taking almost 10 hours for just 2.2 million rows in a table. Table
> does not
> > have any indexes or triggers.
>
> Well, sections 14.4 and 14.5 might help:
>
> https://www.postgresql.org/docs/14/performance-tips.html
>
> Your time seems very slow --- are the rows very wide?
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:38 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
@ 2022-03-05 01:22 ` Imre Samu <pella.samu@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Imre Samu @ 2022-03-05 01:22 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
> Correct rows are wider. One of the columns is text and one is bytea.
with the PG14 the LZ4 compression is worth checking.
via
https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14
*"""INSERT statements with 16 clientsAnother common scenario that I tested
was accessing the database from multiple clients - 16 in this case.What I
found out, as can be seen below, is that compression performance of single
large files (HTML, English text, source code, executable binary, pictures)
using LZ4 was 60% to 70% faster compared to PGLZ, and that there was also a
small improvement while inserting multiple small files (PostgreSQL
document).*
*"""*
kind regards,
Imre
aditya desai <admad123@gmail.com> ezt írta (időpont: 2022. márc. 4., P,
19:42):
> Hi Bruce,
> Correct rows are wider. One of the columns is text and one is bytea.
>
> Regards,
> Aditya.
>
> On Sat, Mar 5, 2022 at 12:08 AM Bruce Momjian <bruce@momjian.us> wrote:
>
>> On Sat, Mar 5, 2022 at 12:01:52AM +0530, aditya desai wrote:
>> > Hi,
>> > One of the service layer app is inserting Millions of records in a
>> table but
>> > one row at a time. Although COPY is the fastest way to import a file in
>> a
>> > table. Application has a requirement of processing a row and inserting
>> it into
>> > a table. Is there any way this INSERT can be tuned by increasing
>> parameters? It
>> > is taking almost 10 hours for just 2.2 million rows in a table. Table
>> does not
>> > have any indexes or triggers.
>>
>> Well, sections 14.4 and 14.5 might help:
>>
>> https://www.postgresql.org/docs/14/performance-tips.html
>>
>> Your time seems very slow --- are the rows very wide?
>>
>> --
>> Bruce Momjian <bruce@momjian.us> https://momjian.us
>> EDB https://enterprisedb.com
>>
>> If only the physical world exists, free will is an illusion.
>>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
@ 2022-03-04 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-04 18:47 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
2022-03-04 18:52 ` Re: Any way to speed up INSERT INTO Andres Freund <andres@anarazel.de>
2022-03-08 13:06 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
1 sibling, 3 replies; 11+ messages in thread
From: Tom Lane @ 2022-03-04 18:42 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
aditya desai <admad123@gmail.com> writes:
> One of the service layer app is inserting Millions of records in a table
> but one row at a time. Although COPY is the fastest way to import a file in
> a table. Application has a requirement of processing a row and inserting it
> into a table. Is there any way this INSERT can be tuned by increasing
> parameters? It is taking almost 10 hours for just 2.2 million rows in a
> table. Table does not have any indexes or triggers.
Using a prepared statement for the INSERT would help a little bit.
What would help more, if you don't expect any insertion failures,
is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
around each batch of 100 or 1000 or so insertions). There's not
going to be any magic bullet that lets you get away without changing
the app, though.
It's quite possible that network round trip costs are a big chunk of your
problem, in which case physically grouping multiple rows into each INSERT
command (... or COPY ...) is the only way to fix it. But I'd start with
trying to reduce the transaction commit overhead.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-03-04 18:47 ` Bruce Momjian <bruce@momjian.us>
2022-03-05 07:02 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2022-03-04 18:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: aditya desai <admad123@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Fri, Mar 4, 2022 at 01:42:39PM -0500, Tom Lane wrote:
> aditya desai <admad123@gmail.com> writes:
> > One of the service layer app is inserting Millions of records in a table
> > but one row at a time. Although COPY is the fastest way to import a file in
> > a table. Application has a requirement of processing a row and inserting it
> > into a table. Is there any way this INSERT can be tuned by increasing
> > parameters? It is taking almost 10 hours for just 2.2 million rows in a
> > table. Table does not have any indexes or triggers.
>
> Using a prepared statement for the INSERT would help a little bit.
Yeah, I thought about that but it seems it would only minimally help.
> What would help more, if you don't expect any insertion failures,
> is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
> around each batch of 100 or 1000 or so insertions). There's not
> going to be any magic bullet that lets you get away without changing
> the app, though.
Yeah, he/she could insert via multiple rows too:
CREATE TABLE test (x int);
INSERT INTO test VALUES (1), (2), (3);
> It's quite possible that network round trip costs are a big chunk of your
> problem, in which case physically grouping multiple rows into each INSERT
> command (... or COPY ...) is the only way to fix it. But I'd start with
> trying to reduce the transaction commit overhead.
Agreed, turning off synchronous_commit for that those queries would be
my first approach.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
2022-03-04 18:47 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
@ 2022-03-05 07:02 ` aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: aditya desai @ 2022-03-05 07:02 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Thanks all for your inputs. We will try to implement inserts in single
transaction. I feel that is the best approach.
Thanks,
AD.
On Saturday, March 5, 2022, Bruce Momjian <bruce@momjian.us> wrote:
> On Fri, Mar 4, 2022 at 01:42:39PM -0500, Tom Lane wrote:
> > aditya desai <admad123@gmail.com> writes:
> > > One of the service layer app is inserting Millions of records in a
> table
> > > but one row at a time. Although COPY is the fastest way to import a
> file in
> > > a table. Application has a requirement of processing a row and
> inserting it
> > > into a table. Is there any way this INSERT can be tuned by increasing
> > > parameters? It is taking almost 10 hours for just 2.2 million rows in a
> > > table. Table does not have any indexes or triggers.
> >
> > Using a prepared statement for the INSERT would help a little bit.
>
> Yeah, I thought about that but it seems it would only minimally help.
>
> > What would help more, if you don't expect any insertion failures,
> > is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
> > around each batch of 100 or 1000 or so insertions). There's not
> > going to be any magic bullet that lets you get away without changing
> > the app, though.
>
> Yeah, he/she could insert via multiple rows too:
>
> CREATE TABLE test (x int);
> INSERT INTO test VALUES (1), (2), (3);
>
> > It's quite possible that network round trip costs are a big chunk of your
> > problem, in which case physically grouping multiple rows into each INSERT
> > command (... or COPY ...) is the only way to fix it. But I'd start with
> > trying to reduce the transaction commit overhead.
>
> Agreed, turning off synchronous_commit for that those queries would be
> my first approach.
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-03-04 18:52 ` Andres Freund <andres@anarazel.de>
2 siblings, 0 replies; 11+ messages in thread
From: Andres Freund @ 2022-03-04 18:52 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; aditya desai <admad123@gmail.com>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi,
On March 4, 2022 10:42:39 AM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>aditya desai <admad123@gmail.com> writes:
>> One of the service layer app is inserting Millions of records in a table
>> but one row at a time. Although COPY is the fastest way to import a file in
>> a table. Application has a requirement of processing a row and inserting it
>> into a table. Is there any way this INSERT can be tuned by increasing
>> parameters? It is taking almost 10 hours for just 2.2 million rows in a
>> table. Table does not have any indexes or triggers.
>
>Using a prepared statement for the INSERT would help a little bit.
>What would help more, if you don't expect any insertion failures,
>is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
>around each batch of 100 or 1000 or so insertions). There's not
>going to be any magic bullet that lets you get away without changing
>the app, though.
>
>It's quite possible that network round trip costs are a big chunk of your
>problem, in which case physically grouping multiple rows into each INSERT
>command (... or COPY ...) is the only way to fix it. But I'd start with
>trying to reduce the transaction commit overhead.
Pipelining could also help.
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-03-08 13:06 ` aditya desai <admad123@gmail.com>
2022-03-08 14:53 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
2 siblings, 1 reply; 11+ messages in thread
From: aditya desai @ 2022-03-08 13:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Hi Tom,
I added BEGIN and COMMIT as shown below around insert and executed it from
pgadmin for 100,000 rows. It ran in just 1 min.
BEGIN;
INSERT INTO TABLE VALUES(....);
INSERT INTO TABLE VALUES(....);
.
,
COMMIT;
However when I run above from psql by passing it to psql(As shown below) as
a file. It still takes a lot of time. Am I doing anything wrong? How can I
run this from pgadmin within a minute?
psql -h host -U user -p Port -d database < INSERT_FILE.sql
PSQL is still printing as below.
INSERT 0 1
INSERT 0 1
Regards,
Aditya.
On Sat, Mar 5, 2022 at 12:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> aditya desai <admad123@gmail.com> writes:
> > One of the service layer app is inserting Millions of records in a table
> > but one row at a time. Although COPY is the fastest way to import a file
> in
> > a table. Application has a requirement of processing a row and inserting
> it
> > into a table. Is there any way this INSERT can be tuned by increasing
> > parameters? It is taking almost 10 hours for just 2.2 million rows in a
> > table. Table does not have any indexes or triggers.
>
> Using a prepared statement for the INSERT would help a little bit.
> What would help more, if you don't expect any insertion failures,
> is to group multiple inserts per transaction (ie put BEGIN ... COMMIT
> around each batch of 100 or 1000 or so insertions). There's not
> going to be any magic bullet that lets you get away without changing
> the app, though.
>
> It's quite possible that network round trip costs are a big chunk of your
> problem, in which case physically grouping multiple rows into each INSERT
> command (... or COPY ...) is the only way to fix it. But I'd start with
> trying to reduce the transaction commit overhead.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
2022-03-08 13:06 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
@ 2022-03-08 14:53 ` Bruce Momjian <bruce@momjian.us>
2022-03-09 06:09 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Bruce Momjian @ 2022-03-08 14:53 UTC (permalink / raw)
To: aditya desai <admad123@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Tue, Mar 8, 2022 at 06:36:17PM +0530, aditya desai wrote:
> Hi Tom,
> I added BEGIN and COMMIT as shown below around insert and executed it from
> pgadmin for 100,000 rows. It ran in just 1 min.
>
> BEGIN;
> INSERT INTO TABLE VALUES(....);
> INSERT INTO TABLE VALUES(....);
> .
> ,
> COMMIT;
>
> However when I run above from psql by passing it to psql(As shown below) as a
> file. It still takes a lot of time. Am I doing anything wrong? How can I run
> this from pgadmin within a minute?
>
> psql -h host -U user -p Port -d database < INSERT_FILE.sql
>
> PSQL is still printing as below.
> INSERT 0 1
> INSERT 0 1
Uh, they should be the same. You can turn on log_statement=all on the
server and look at what queries are being issued in each case.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
If only the physical world exists, free will is an illusion.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Any way to speed up INSERT INTO
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:42 ` Re: Any way to speed up INSERT INTO Tom Lane <tgl@sss.pgh.pa.us>
2022-03-08 13:06 ` Re: Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-08 14:53 ` Re: Any way to speed up INSERT INTO Bruce Momjian <bruce@momjian.us>
@ 2022-03-09 06:09 ` aditya desai <admad123@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: aditya desai @ 2022-03-09 06:09 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
Ok Will check. But from pgadmin it takes 1min and by psql it is taking 20
mins for 100,000 rows with BEGIN; COMMIT;
Thanks,
Aditya.
On Tue, Mar 8, 2022 at 8:23 PM Bruce Momjian <bruce@momjian.us> wrote:
> On Tue, Mar 8, 2022 at 06:36:17PM +0530, aditya desai wrote:
> > Hi Tom,
> > I added BEGIN and COMMIT as shown below around insert and executed it
> from
> > pgadmin for 100,000 rows. It ran in just 1 min.
> >
> > BEGIN;
> > INSERT INTO TABLE VALUES(....);
> > INSERT INTO TABLE VALUES(....);
> > .
> > ,
> > COMMIT;
> >
> > However when I run above from psql by passing it to psql(As shown below)
> as a
> > file. It still takes a lot of time. Am I doing anything wrong? How can I
> run
> > this from pgadmin within a minute?
> >
> > psql -h host -U user -p Port -d database < INSERT_FILE.sql
> >
> > PSQL is still printing as below.
> > INSERT 0 1
> > INSERT 0 1
>
> Uh, they should be the same. You can turn on log_statement=all on the
> server and look at what queries are being issued in each case.
>
> --
> Bruce Momjian <bruce@momjian.us> https://momjian.us
> EDB https://enterprisedb.com
>
> If only the physical world exists, free will is an illusion.
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2022-03-09 06:09 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-04 18:31 Any way to speed up INSERT INTO aditya desai <admad123@gmail.com>
2022-03-04 18:38 ` Bruce Momjian <bruce@momjian.us>
2022-03-04 18:42 ` aditya desai <admad123@gmail.com>
2022-03-05 01:22 ` Imre Samu <pella.samu@gmail.com>
2022-03-04 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-03-04 18:47 ` Bruce Momjian <bruce@momjian.us>
2022-03-05 07:02 ` aditya desai <admad123@gmail.com>
2022-03-04 18:52 ` Andres Freund <andres@anarazel.de>
2022-03-08 13:06 ` aditya desai <admad123@gmail.com>
2022-03-08 14:53 ` Bruce Momjian <bruce@momjian.us>
2022-03-09 06:09 ` aditya desai <admad123@gmail.com>
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