Re: Query suddenly taking longer....

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(at)hotdogrecords(dot)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
>
>