Re: strange performance problem

[Richard Huxton <dev(at)archonet(dot)com>]

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

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.

-- 
  Richard Huxton
  Archonet Ltd