[postgis-devel] GUI Speed

[Paul Ramsey]

Mark,
The main point in the profile, is that the majority of time is not
being spent in the loader at all, it's being spent in the postgres
process. And oddly, the gui and the cli are exercising very
*different* parts of the postgres code.
P.

On Mon, Jul 12, 2010 at 4:38 AM, Mark Cave-Ayland
<mark.cave-ayland at siriusit.co.uk> wrote:
> 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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>