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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
