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
