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

Fernando Schapachnik <fpscha(at)ns1(dot)via-net-works(dot)net(dot)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