From: Kurt Overberg <kurt@hotdogrecords.com>
To: pgsql-sql@postgresql.org
Subject: Query suddenly taking longer....
Date: Mon, 11 Aug 2003 12:51:31 -0400
Message-ID: <3F37C993.3070502@hotdogrecords.com> (raw)
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
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
Subject: Re: Query suddenly taking longer....
In-Reply-To: <3F37C993.3070502@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