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

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
for the query plan being used?

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
query to run reasonably quickly using a mergejoin, but mergejoin
needs indexes on the fields being joined.  (The system will also
consider doing an explicit sort and then a mergejoin, but obviously
the sort step takes extra time.)

If you haven't vacuumed since filling the tables then the optimizer
may believe that the tables only contain a few rows, in which case
it's likely to use a plain nested-loop join (ie, compare every usarios
row to every passwd row to find matching id_usr fields).  That's nice
and fast for little tables, but a big loser on big ones...
        regards, tom lane