Hello i have the same table with the same data in my development machine and in
a small server in production. The table is this:
Tabla «modelo_subfamilia»
Columna | Tipo | Modificadores
---------------+-----------------------+---------------
nombre | character varying(40) | not null
subfamilia_id | character(4) | not null
id_familia | character(4) | not null
hasta | character(4) | not null
foto | bytea |
id_seccion | integer |
id_categoria | integer |
Índices:
«modelo_subfamilia_pkey» PRIMARY KEY, btree (subfamilia_id)
«uq_hasta_index_modsubfam» UNIQUE, btree (hasta)
«nombre_index_modsubfam» btree (nombre)
the column "foto" has a toast table, aside from the fact that it have in the
server three triggers they are exactly the same, with the same data too, my
development machine has version 8.3.6 (linux kernel 2.6.28) and production
server has version 8.3.3 (linux kernel 2.6.26), the settings in postgresql.conf
are nearly the same except for work_men (24 server, 36 development machine) and
effective_cache_size (1024 server, 1536 development machine), they have the same
sysctl settings and limits too, and the same mount options for the ext3
filesystem that have the data, i have a single sata disk (wd velociraptor) in my
development machine and the server it is using a linux software raid10 with 4
sata disks.
I have detected that a simple query from the application i am developing in QT
it is really fast in my machine and takes too much time in production server, i
am logging the queries that spend more than 500ms so i have this in the log.
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"
so i have been testing in my machine and in the server the same query to see the
difference.
EXPLAIN ANALYZE:
-development:
Seq Scan on modelo_subfamilia (cost=0.00..11.68 rows=368 width=73) (actual
time=0.010..0.092 rows=368 loops=1)
Total runtime: 0.174 ms
-server:
Seq Scan on modelo_subfamilia (cost=0.00..6.10 rows=368 width=69) (actual
time=0.008..0.158 rows=368 loops=1)
Total runtime: 0.289 ms
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 so the difference its there anyway but not
in psql commandline it seems to be only when accessing from a graphical front
end, and with the complete query with foto column included i get the postgresql
process to eat 90% of the cpu for the complete 4 seconds that it gets to send me
the result so it not seems to be a problem with the cpu usage from the graphic
libs (no QT or WxWindows), how could i debug this problem?, where should i begin
to search? Thanks.
Regards,
Miguel Angel.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
