Message-ID: <3F37C993.3070502@hotdogrecords.com>
Date: Mon, 11 Aug 2003 12:51:31 -0400
From: Kurt Overberg <kurt@hotdogrecords.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.3) Gecko/20030312
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Query suddenly taking longer....
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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