Re: strange performance problem

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.
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.
>
>
> First things first: run "VACUUM FULL VERBOSE modela_subfamilia" on both
> systems and see how many pages were being taken up. I'm guessing it will
> be more on the server, but is it a *lot* more?
>
> Then run "REINDEX TABLE modela_subfamilia" to clean up your indexes.
>
> If it's still a problem that suggests the two systems are doing
> something different with the bytea encoding/decoding. Check:
>
> 1. Connection settings - is one tcp/ip and the other unix sockets?
> 2. Binary/text mode - are you using a binary format for query results on
> the development machine?
> 3. Are you fetching the entire result-set on the server and only the
> first row(s) on your dev. machine?
> 4. Encoding/locale differences - can't see how this would matter for
> bytea, but worth ruling out.
>

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

1- The same in the two machines, tcp/ip with localhost.
2- I am exactly the same code in the two machines and the same pgadmin3 version too.
3- Ever the entire result set.
4- I am using es_ES.UTF8 in the two machines

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

Regards,
Miguel Angel.