From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query suddenly taking longer....
Date: Mon, 11 Aug 2003 13:45:04 -0400
Message-ID: <4013.1060623904@sss.pgh.pa.us> (raw)
In-Reply-To: <3F37C993.3070502@hotdogrecords.com>
References: <3F37C993.3070502@hotdogrecords.com>
Kurt Overberg <kurt@hotdogrecords.com> writes:
> I have the following query on postgresql 7.3.2 on RedHat 7.
> select *, (select count(*) from xrefmembergroup where membergroupid =
> m.id) as numberingroup from membergroup m;
> The xrefmembergroup table has about 120,000 rows, membergroup has 90.
> This query has been running very quickly, but has suddenly started
> taking a LONG LONG time.
Presumably the plan changed, but without any reasonable way to tell what
the old plan was, there's no way to be sure. (Possibly comparing
explain plans from both servers would be useful, though.)
> Now, when I do run this query my postmaster process spikes from around
> 10Megs (normal size) to around 250Megs and just kinda sits there until
> it eventually returns 5 minutes later.
What was the new plan, exactly? I don't see any reason for this query
to chew a lot of memory.
I think that the query is inherently inefficient as written, since
it forces a separate scan of xrefmembergroup for every membergroup row.
I don't really see how it could ever have been done in subsecond time,
unless perhaps a large fraction of the xrefmembergroup entries did not
match any membergroup row, which seems unlikely.
I'd suggest doing something that will allow the counts to be accumulated
in just one xrefmembergroup scan, with GROUP BY. A straightforward way
is
select m.*, numberingroup
from
membergroup m,
(select membergroupid, count(*) as numberingroup
from xrefmembergroup group by membergroupid) as c
where m.id = c.membergroupid;
I'm not convinced this will actually be much of a win in 7.3
unfortunately ... but it should fly in 7.4, because of the new
hash aggregation code.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, kurt@hotdogrecords.com
Subject: Re: Query suddenly taking longer....
In-Reply-To: <4013.1060623904@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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