Re: slow subselects

"Marko Niinimaki" <manzikki@gmail.com> writes:
> I'm having a surprising performance problem with the following simple
> 'highscore report'

> select studentid, (select max(score) from
> studentprofile prof where prof.studentid = students.studentid) from students;

> I have indexes on students(studentid) and studentprofile(studentid).

The optimal index for this would be on studentprofile(studentid,score).
A quick test says that PG 8.1 knows what to do with such an index ---
what does EXPLAIN show for this query?

            regards, tom lane