Re: [PERFORM] insert performance for win32

["Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>]

Tom Lane wrote:
> Just to be clear: what you were testing was
> 	BEGIN;
> 	INSERT ... VALUES (...);
> 	repeat insert many times
> 	COMMIT;
roger.

> with each statement issued as a separate PQexec() operation, correct?
> Was this set up as a psql script, or specialized C code?  (If a psql
> script, I wonder whether it's psql that's chewing the time.)

I thought that too.  I'm running dump file piped to psql in quiet mode,
output redirected to file.  Here is the profile for psql:

Each sample counts as 0.01 seconds.
  %   cumulative   self              self     total           
 time   seconds   seconds    calls   s/call   s/call  name    
 40.74      1.65     1.65   499988     0.00     0.00  yylex
 10.37      2.07     0.42   249994     0.00     0.00  SendQuery
  7.41      2.37     0.30  1499959     0.00     0.00  GetVariable
  7.16      2.66     0.29        1     0.29     3.71  MainLoop
  4.94      2.86     0.20   499988     0.00     0.00  psql_scan
  3.95      3.02     0.16   249999     0.00     0.00  SetVariable
  3.46      3.16     0.14  5999686     0.00     0.00  emit
  2.47      3.26     0.10                             pg_strcasecmp

which tells nothing.  I think next step is to write libpq app running
the test case and compare execparams vs. exec vs. psql.  win32 has
excellent hardware timer which I can use.

The mingw gprof self seconds, btw, are low and unreliable.  One thing I
do know is that I block commented out vast sections of the file /*...*/.
on 200k rec load, times were 
to load 50k recs in a single transaction, with 2nd and 3rd 50 recs
commented.
          commented  time
block 1       no      30 
block 2       yes     39 
block 3       yes     24
block 4       no      69

this suggests psql read time is constant (but rather slow) but per
insert time grows.

> > 2. ran a 50k profile vs. 250k profile.  Nothing jumps out as being
> > slower or faster: most time is spent in yyparse on either side.
From
> > this my preliminary conclusion is that there is something going on
in
> > the win32 api which is not showing in the profile.
> 
> Hmm.  Client/server data transport maybe?  It would be interesting to
> try inserting the same data in other ways:
> 	* COPY from client

runs super fast (500k recs in a few seconds)

> 	* INSERT/SELECT from another table
> and see whether you see a similar slowdown.

the command I used to generate test file, insert ... select from
generate_series runs super quick, as does create table as select..

merlin