Re: strange performance problem - Mailing list pgsql-general

Linos wrote:
> Richard Huxton escribió:
>> Linos wrote:
>>> 2009-02-27 13:51:15 CET 127.0.0.1LOG:  duración: 4231.045 ms  sentencia:
>>> SELECT "nombre", "subfamilia_id", "id_familia", "hasta", "foto",
>>> "id_seccion", "id_categoria" FROM "modelo_subfamilia"
>>
>>> PSQL with \timing:
>>> -development: Time: 72,441 ms
>>> -server: Time: 78,762 ms
>>
>>> but if i load it from QT or from pgadmin i get more than 4 seconds in
>>> server and ~100ms in develoment machime, if i try the query without the
>>> "foto" column i get 2ms in development and 30ms in server
>>
>> OK, so:
>> 1. No "foto"     - both quick
>> 2. psql + "foto" - both slow
>> 3. QT + "foto"   - slow only on server
>
> 1.No "foto"                     -both quick but still a noticeable
> difference between them 2ms develoment - 30ms server
> 2. psql + "foto"                -both quick really, they are about 70ms,
> not bad giving that foto are bytea with small png images.

Ah, sorry - I read the psql timings as 72 thousand ms (72 seconds) - of
course you're using European decimal marks.

> 3. QT or WXWindows + "foto"       -slow only one server yes.
>
>> The bit that puzzles me is why both are slow in #2 and not in #3.

OK - well, the fact that both psql are fast means there's nothing too
wrong with your setup. It must be something to do with the application
libraries.

> After the vacuum full verbose and reindex still the same problem (i had
> tried the vacuum before).

OK. Worth ruling it out.

> 1- The same in the two machines, tcp/ip with localhost.

Hmm...

> 2- I am exactly the same code in the two machines and the same pgadmin3
> version too.

Good. We can rule that out.

> 3- Ever the entire result set.

Good.

> 4- I am using es_ES.UTF8 in the two machines

Good.

> What can be using wxwindows and QT to access postgresql that psql it is
> not using, libpq?

Well, I'm pretty sure that pgadmin will be using libpq at some level,
even if there is other code above it.

Either:

1. One machine (the fast one) is actually using unix sockets and not
tcp/ip+localhost like you think.
2. The networking setup is different on each.
3. Something your code is doing with the bytea data is slower on one
machine than another. I seem to remember that pgadmin used to be quite
slow at displaying large amounts of data. They did some work on that,
but it might be that your use-case still suffers from it.

For #1 try the psql test again, but with "-h localhost" and "-h /tmp"
(or whatever directory your unix socket is in - might be
/var/run/postgresql or similar too).

For #2, you can always try timing "psql -h localhost ... > /dev/null" on
both machines. If you capture port 5432 with something like "tcpdump -w
ip.dump host localhost and port 5432" you can then use wireshark to see
exactly why it's slow.

For #3, I guess you'd need to reduce your code to just fetching the data
and time that. You may have already done this of course.

HTH

--
  Richard Huxton
  Archonet Ltd