[postgis-devel] GUI Speed

[Mark Cave-Ayland]

Paul Ramsey wrote:

>> i) By default, shp2pgsql .... | psql -d foo  will use local UNIX sockets
>> rather than TCP/IP. Does it make a difference if you do something like
>> shp2pgsql .... | psql -d foo -h localhost to force TCP/IP transport?
> 
> This I haven't tested, but the actual functions exercised by the
> postgres backend should not differ depending on transport, and they
> definitely do.

Sure, but in the GUI case you are writing over a network socket whereas 
for the CLI version you are streaming the INSERTs/COPYs over a pipe. So 
there is bound to be some speed difference.

I think it's a little tricky to do comparisons without the corresponding 
  traces for the shp2pgsql CLI version too. And also both client & 
server traces are required in order to better understand where the time 
is going.

>> ii) Does the GUI use COPY or INSERT format by default? Way back in my mind I
>> had a feeling that shp2pgsql defaulted to COPY whereas the GUI defaulted to
>> INSERT.
> 
> They both default to INSERT, but in this case I tested them both in COPY mode.
> 
> I had a thought, which is we are calling the PQ*copy function
> once-per-line. I wonder if psql sends data back in larger blocks? If
> we buffered up the lines and called the function less often, would
> that change things, I wonder.

I don't think this is relevant here though, since the CLI and GUI 
versions both have very similar main loops. Other possible things I can 
see that might take time are:

1) The GTK event processing functions
2) pgui_exec / pgui_copy_write (in particular sending the separate 
newline as a separate PQputCopyData statement)

But again, without comparative profiling data then it's impossible to tell.


ATB,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs