Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 72DE09FA617 for ; Tue, 20 Feb 2007 03:10:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 88605-08 for ; Tue, 20 Feb 2007 03:10:55 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.229]) by postgresql.org (Postfix) with ESMTP id 9DC569FA136 for ; Tue, 20 Feb 2007 03:10:55 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so2303787wxd for ; Mon, 19 Feb 2007 23:10:55 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ARAjJScOww8pkAaUzt17MekIfaFlCkfhO9y/8ipMD30tKNIR4LbLUYHKYbpOq+SeyXDGxSculD6Zr5frI9pJg62bty9QxhoKDZo5ia4ghCWzm9eRE6QiGI1xcU8AUo6JavGsmpWDlW+5BIKLWdRKeOjieWqKsaTkjs9Ln2wG6FY= Received: by 10.90.88.13 with SMTP id l13mr8168319agb.1171955455172; Mon, 19 Feb 2007 23:10:55 -0800 (PST) Received: by 10.90.80.18 with HTTP; Mon, 19 Feb 2007 23:10:55 -0800 (PST) Message-ID: <58b7e6510702192310v77517652q4ba8ecaf50ea161c@mail.gmail.com> Date: Tue, 20 Feb 2007 16:10:55 +0900 From: "Marko Niinimaki" To: pgsql-performance@postgresql.org Subject: slow subselects In-Reply-To: <58b7e6510702192302i6b526e56wee847a2122cec3aa@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <58b7e6510702192302i6b526e56wee847a2122cec3aa@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/198 X-Sequence-Number: 23252 Hello, 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). Row counts: about 160 000 in each students and studentprofile. Postgres version: postgresql-8.1.8-1.fc5 postgresql-server-8.1.8-1.fc5 This is a dual-processor 3Ghz 64bit box with 2 GB mem. Running the query takes 99% CPU and 1% mem. I have the same data in MSSQL and there the query takes less than a minute. With postgres it seems to take several hours. Is there a way of making this faster? Marko