Thread: Query suddenly taking longer....

Hi all,

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;

(basically- try to get a list of 'groups' and the number of members in
each group)
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.  Nothing has else has really changed in the
system, this morning it just started taking too long (went from .5
seconds to > 5 minutes).

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.

I get the feeling that the xrefmembergroup table has crossed some bounds
(disk/memory) that is causing it to be super slow, but I don't know
which one.  I have b-tree indexes on all the fields in xrefmembergroup. Here's the table definition:
    Column     |           Type           |      Modifiers
---------------+--------------------------+------------------------------------ id            | integer
| not null default
 
nextval('"xrefmembergroup_id_seq"'::text) membergroupid | integer                  | not null default 0 memberid      |
integer                 | not null default 0 timestamp     | timestamp with time zone | default
 
"timestamp"('now'::text)
Indexes: xrefmembergroup_pkey primary key btree (id),         membergroupid_xrefmembergroup_key btree (membergroupid),
      memberid_xrefmembergroup_key btree (memberid)
 

At one point, I did an EXPLAIN ANALYZE on the query and it seemed to be
using sequential scans.  I can't run this query anymore because it nukes
my production server, so I'm limited in how much I can debug this right
now.  I have a similar system (7.3.2 on Debian) that does not exhibit
this problem running on the same database.  Don't know why its not using
the indexes.  Any thoughts?

/kurt

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

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
> 
>