Re: Any way to speed up INSERT INTO
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 wrote: > aditya desai 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 >
Re: Any way to speed up INSERT INTO
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 https://momjian.us EDB https://enterprisedb.com If only the physical world exists, free will is an illusion.
Re: Any way to speed up INSERT INTO
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 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 https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > >
