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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

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:

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?



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-2017 The PostgreSQL Global Development Group