pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedslow subselects
4+ messages / 3 participants
[nested] [flat]
* slow subselects
@ 2007-02-20 07:10 Marko Niinimaki <manzikki@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Marko Niinimaki @ 2007-02-20 07:10 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow subselects
@ 2007-02-20 07:21 ismo.tuononen@solenovo.fi
parent: Marko Niinimaki <manzikki@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: ismo.tuononen@solenovo.fi @ 2007-02-20 07:21 UTC (permalink / raw)
To: Marko Niinimaki <manzikki@gmail.com>; +Cc: pgsql-performance
try:
select studentid,max(score) from studentprofile group by studentid;
or if you want only those which exists in students
select s.studentid,max(p.score)
from studentprofile p,students s
where s.studentid=p.studentid
group by s.studentid;
if it takes longer than 1-2 seconds something is seriously wrong
Ismo
On Tue, 20 Feb 2007, Marko Niinimaki wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow subselects
@ 2007-02-20 07:43 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marko Niinimaki <manzikki@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-02-20 07:43 UTC (permalink / raw)
To: Marko Niinimaki <manzikki@gmail.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: slow subselects
@ 2007-02-21 05:34 Marko Niinimaki <manzikki@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Marko Niinimaki @ 2007-02-21 05:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Many thanks! Ismo's reply solved the problem, and Tom's reply solved
another one.
Marko
Ismo Tuononen wrote:
> select studentid,max(score) from studentprofile group by studentid;
On 20/02/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The optimal index for this would be on studentprofile(studentid,score).
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-02-21 05:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-20 07:10 slow subselects Marko Niinimaki <manzikki@gmail.com>
2007-02-20 07:21 ` ismo.tuononen@solenovo.fi
2007-02-20 07:43 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-21 05:34 ` Marko Niinimaki <manzikki@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox