Re: [HACKERS] Neverending query on 6.5.2 over Solaris 2.5.1

En un mensaje anterior, Tom Lane escribió:
> Fernando Schapachnik <fpscha@ns1.via-net-works.net.ar> writes:
> >     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.
> 
> Have you vacuumed the database lately?  What does "explain ..." show

I did this today. I also installed Postgres on a FreeBSD machine 
(comparable -and low- load averages) and updated the version to 6.5.2.

After vacuum:
On the Sun: 1 minute.
On the FreeBSD: 12 seconds.

Explain shows (on both machines):

operaciones=> explain SELECT u.nombre_cuenta, per.nombre, 
pas.clave_cifrada, pas.clave_plana, u.estado FROM usuarios u, perfiles per,
passwd pas WHERE (u.activa) and (u.perfil=per.id_perfil) and 
(u.id_usr=pas.id_usr) \g
NOTICE:  QUERY PLAN:

Nested Loop  (cost=503.74 rows=1 width=74) ->  Nested Loop  (cost=500.89 rows=1 width=58)       ->  Seq Scan on
usuariosu  (cost=498.84 rows=1 width=30)       ->  Index Scan using passwd_id_usr_key on passwd pas  
 
(cost=2.05 rows=10571 width=28) ->  Seq Scan on perfiles per  (cost=2.85 rows=56 width=16)

EXPLAIN 
> You might be well advised to create indexes on usarios.id_usr and
> passwd.id_usr, if you don't have them already.  I'd expect this

As usuarios.id_usr and passwd.id_usr are both serial, they have 
indexes automatically created (I double checked that). PgAccess shows 
that usuarios has no primary key (I don't know why) and that 
usuarios_id_usr_key is an unique, no clustered index. Same on passwd.

I'm running postmaster -N 8 -B 16 because whitout these postmaster 
wouldn't get all the shared memory it needed and won't start. Do you 
think that this may be in some way related?

Thanks for your help!

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