Skip site navigation (1) Skip section navigation (2)

slow subselects

From: "Marko Niinimaki" <manzikki(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow subselects
Date: 2007-02-20 07:10:55
Message-ID: 58b7e6510702192310v77517652q4ba8ecaf50ea161c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: ismo.tuononenDate: 2007-02-20 07:21:45
Subject: Re: slow subselects
Previous:From: Tom LaneDate: 2007-02-20 04:54:08
Subject: Re: Query Optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group