From: Kurt Overberg <kurt@hotdogrecords.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query suddenly taking longer....
Date: Mon, 11 Aug 2003 15:44:57 -0400
Message-ID: <3F37F239.4060007@hotdogrecords.com> (raw)
In-Reply-To: <4013.1060623904@sss.pgh.pa.us>
References: <3F37C993.3070502@hotdogrecords.com>
<4013.1060623904@sss.pgh.pa.us>
Tom,
Thanks for the reply. I agree that the query seemed inefficient, but it
ran so quickly I thought it was okay. The only difference between the
two servers was that the fast one used an Index Scan while the other
(the now-slow one) would use a sequential scan. The query as you
re-wrote it seems to work great though. Thank you.
/kurt
Tom Lane wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
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: kurt@hotdogrecords.com, tgl@sss.pgh.pa.us
Subject: Re: Query suddenly taking longer....
In-Reply-To: <3F37F239.4060007@hotdogrecords.com>
* 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