Re: strange performance problem

[Linos <info(at)linos(dot)es>]

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.