PSQL is slower than PgAdmin III

Question

I am profiling part of my application and I found out that PgAdmin3 is significantly faster than psql when executing the same script. I use unix sockets, Ubuntu 12.04 and Postgresql 9.1 and pgadmin3 v1.14. I have a simple script as shown below:

BEGIN INSERT INTO key_value(section,key,value) VALUES('section','key1','value'); .... INSERT INTO key_value(section,key,value) VALUES('section','key10000','value'); COMMIT; 

Here is the table:

 CREATE TABLE key_value ( key text NOT NULL, value text, CONSTRAINT key_value_pkey PRIMARY KEY (section , key ) ) WITH ( OIDS=FALSE ); 

This script has 10,000 inserts. Running it in pgadmin3 takes about 0.5 seconds, running it in psql takes 2.5 ~ 3.5 seconds. Both are wrapped in a transaction, so there should be no difference. The results are consistent for re-creating the table, full vacuum, etc. Logging into the Postgresql server shows that postgres executes a log for each insert statement when executed in psql, but only one log when executed from pgadmin3.

Psql execution is performed with the following:

 psql -n -t -f p.sql -o/dev/null 

and also verified using

 psql -n -t -1 -f p.sql -o/dev/null 

My question is why psql is so slower and why we postgresql register every statement in one client, but only the whole transaction in another, and there is a simple fix for psql there.

EDIT To clarify, I am recording the duration, not the instruction:

On the server, I get the following when executed with psql

 2012-10-02 12:20:32 CEST LOG: duration: 0.283 ms .... 2012-10-02 12:20:35 CEST LOG: duration: 0.285 ms 2012-10-02 12:20:35 CEST LOG: duration: 0.291 ms 2012-10-02 12:20:35 CEST LOG: duration: 0.279 ms 2012-10-02 12:20:35 CEST LOG: duration: 0.284 ms 2012-10-02 12:20:35 CEST LOG: duration: 0.279 ms 2012-10-02 12:20:35 CEST LOG: duration: 0.299 ms .... 2012-10-02 12:20:36 CEST LOG: duration: 5.779 ms 

When executed using pgadmin3:

 2012-10-02 12:23:21 CEST LOG: duration: 532.695 ms 

User and database are the same in psql and pgadmin3

Answer 1

My advice: pgAdmin execute the script as one multi-step, so the network (protocol) overhead is less.

Answer 2

psql has an integrated parser that recognizes the comma as a statement delimiter. It uses it to split the SQL script into several statements, unlike pgadmin, as said in Pavel's answer .

There is no way to disable this behavior. However, the use of an anonymous DO code block should lead to similar results with minor changes. If you wrote a script as follows:

 DO $$ BEGIN INSERT INTO key_value(section,key,value) VALUES('section','key1','value'); .... INSERT INTO key_value(section,key,value) VALUES('section','key10000','value'); END $$; 

This will be sent to the server at a time, as with pgadmin (although this is technically quite different) and will supposedly be done at a comparable time.

EDIT: according to @vseguip's comment, this is not efficient due to the time spent by the plpgsql interpreter on these 10k statements.

ALTERNATIVE IDEA: change the script to have only one INSERT with multiple rows, not multiple INSERT, without a DO block.

 BEGIN; INSERT INTO key_value(section,key,value) VALUES('section','key1','value'), ('section','key2','value'), ('section','key3','value'), .... ('section','key10000','value'); END;