Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id CA1EF9FB39F for ; Tue, 20 Feb 2007 03:43:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 50842-02-2 for ; Tue, 20 Feb 2007 03:43:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 3AADB9FB554 for ; Tue, 20 Feb 2007 03:43:08 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id l1K7h6Dr022953; Tue, 20 Feb 2007 02:43:06 -0500 (EST) To: "Marko Niinimaki" cc: pgsql-performance@postgresql.org Subject: Re: slow subselects In-reply-to: <58b7e6510702192310v77517652q4ba8ecaf50ea161c@mail.gmail.com> References: <58b7e6510702192302i6b526e56wee847a2122cec3aa@mail.gmail.com> <58b7e6510702192310v77517652q4ba8ecaf50ea161c@mail.gmail.com> Comments: In-reply-to "Marko Niinimaki" message dated "Tue, 20 Feb 2007 16:10:55 +0900" Date: Tue, 20 Feb 2007 02:43:06 -0500 Message-ID: <22952.1171957386@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/200 X-Sequence-Number: 23254 "Marko Niinimaki" 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