Query not ending on 6.5.0 over Solaris 2.5.1 SPARC
Hello:
Maybe somebody could give some clue about what is happening:
I have 6.5.0 running over Solaris 2.5.1 SPARC. I have a
database with 5 tables, 3 of them < 100 regs. and 2 ("usuarios" and
"passwd") with >10000. When querying for:
SELECT u.nombre_cuenta, per.nombre, pas.clave_cifrada,
pas.clave_plana, u.estado FROM usuarios u, perfiles per, passwd pas
WHERE (u.perfil=per.id_perfil) and (u.id_usr=pas.id_usr) and
(u.activa) \g
postmaster starts eating a lot of CPU and it doesn't finish to
process the query in +20 minutes.
Postmaster shows:
[3]: ns2:/>su - pgsql Sun Microsystems Inc. SunOS 5.5.1 Generic May 1996
Sun Microsystems Inc. SunOS 5.5.1 Generic May 1996
[1]: ns2:/usr/local/pgsql>bin/postmaster -i -d 2 -N 8 -B 16 -D /usr/local/pgsql /data FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0] binding ShmemCreate(key=52e2c1, size=359424) bin/postmaster: ServerLoop: handling reading 5 bin/postmaster: ServerLoop: handling reading 5 bin/postmaster: ServerLoop: handling writing 5 bin/postmaster child[2934]: starting with (/usr/local/pgsql/bin/postgres -d2 -B 16 -v131072 -p operaciones ) FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0] debug info: User = admin RemoteHost = localhost RemotePort = 0 DatabaseName = operaciones Verbose = 2 Noversion = f timings = f dates = Normal bufsize = 16 sortmem = 512 query echo = f InitPostgres StartTransactionCommand bin/postmaster: BackendStartup: pid 2934 user admin db operaciones socket 5 query: select version(); ProcessQuery CommitTransactionCommand StartTransactionCommand query: SELECT u.nombre_cuenta, per.nombre, pas.clave_cifrada, pas.clave_plana, u.estado FROM usuarios u, perfiles per, passwd pas WHERE (u.perfil=per.id_perfil) and (u.id_usr=pas.id_usr) and (u.activa) ProcessQuery bin/postmaster: dumpstatus: sock 5 bin/postmaster: dumpstatus: sock 5
/usr/local/pgsql
/data
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
binding ShmemCreate(key=52e2c1, size=359424)
bin/postmaster: ServerLoop: handling reading 5
bin/postmaster: ServerLoop: handling reading 5
bin/postmaster: ServerLoop: handling writing 5
bin/postmaster child[2934]: starting with
(/usr/local/pgsql/bin/postgres -d2
-B 16 -v131072 -p operaciones )
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
User = admin
RemoteHost = localhost
RemotePort = 0
DatabaseName = operaciones
Verbose = 2
Noversion = f
timings = f
dates = Normal
bufsize = 16
sortmem = 512
query echo = f
InitPostgres
StartTransactionCommand
bin/postmaster: BackendStartup: pid 2934 user admin db operaciones
socket 5
query: select version();
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: SELECT u.nombre_cuenta, per.nombre, pas.clave_cifrada,
pas.clave_plana, u.estado FROM usuarios u, perfiles per, passwd pas WHERE
(u.perfil=per.id_perfil) and (u.id_usr=pas.id_usr) and (u.activa)
ProcessQuery
bin/postmaster: dumpstatus:
sock 5
bin/postmaster: dumpstatus:
sock 5
Any hints?
Regards.
Fernando P. Schapachnik
Administraci�n de la red
VIA Net Works Argentina SA
Diagonal Roque S�enz Pe�a 971, 4� y 5� piso.
1035 - Capital Federal, Argentina.
(54-11) 4323-3333
http://www.via-net-works.net.ar