From: Chris Hoover <revoohc@sermonaudio.com>
To: psql-admin@postgresql.org
Cc: revoohc@sermonaudio.com
Subject: Index weirdness - Any ideas why
Date: Tue, 08 Feb 2005 11:27:40 -0500
Message-ID: <4208E87C.3000908@sermonaudio.com> (raw)
We are seeing some strangeness with our indexes. Yesterday, one of our
tell tale queries was performing very badly. We tried to analyze the
table, and even vacuum analyze it, but it did not fix the issue.
The issue was general slowness. After we did the maintenance on it, it
would perform very sporadically. Some times it would run sub second,
and then the next run (seconds apart) would run for several seconds.
Has anyone ever seen anything like this?
Here are some explain analyzes to for detail:
explain analyze
select count(1)
from <table>
where hdr_user_id='username'
and hdr_clm_status in ('H','E','A','R','T','V','P','L');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=11927.83..11927.83 rows=1 width=0) (actual
time=2191.73..2191.73 rows=1 loops=1)
-> Index Scan using <table>_hdr_user_id_idx on <table>
(cost=0.00..11926.53 rows=521 width=0) (actual time=0.31..2190.98
rows=380 loops=1)
Index Cond: (hdr_user_id = 'username'::character varying)
Filter: ((hdr_clm_status = 'H'::bpchar) OR (hdr_clm_status =
'E'::bpchar) OR (hdr_clm_status = 'A'::bpchar) OR (hdr_clm_status
='R'::bpchar) OR (hdr_clm_status = 'T'::bpchar) OR (hdr_clm_status =
'V'::bpchar) OR (hdr_clm_status = 'P'::bpchar) OR (hdr_clm_status =
'L'::bpchar))
Total runtime:2191.84 msec
(5 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=11927.83..11927.83 rows=1 width=0) (actual
time=863.54..863.54 rows=1 loops=1)
-> Index Scan using <table>_hdr_user_id_idx on <table>
(cost=0.00..11926.53 rows=521 width=0) (actual time=0.14..863.22
rows=380 loops=1)
Index Cond: (hdr_user_id = 'username'::character varying)
Filter: ((hdr_clm_status = 'H'::bpchar) OR (hdr_clm_status =
'E'::bpchar) OR (hdr_clm_status = 'A'::bpchar) OR (hdr_clm_status
='R'::bpchar) OR (hdr_clm_status = 'T'::bpchar) OR (hdr_clm_status =
'V'::bpchar) OR (hdr_clm_status = 'P'::bpchar) OR (hdr_clm_status =
'L'::bpchar))
Total runtime: 863.61 msec
(5 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=11927.83..11927.83 rows=1 width=0) (actual
time=454.50..454.50 rows=1 loops=1)
-> Index Scan using <table>_hdr_user_id_idx on <table>
(cost=0.00..11926.53 rows=521 width=0) (actual time=0.27..453.93
rows=380 loops=1)
Index Cond: (hdr_user_id = 'username'::character varying)
Filter: ((hdr_clm_status = 'H'::bpchar) OR (hdr_clm_status =
'E'::bpchar) OR (hdr_clm_status = 'A'::bpchar) OR (hdr_clm_status
='R'::bpchar) OR (hdr_clm_status = 'T'::bpchar) OR (hdr_clm_status =
'V'::bpchar) OR (hdr_clm_status = 'P'::bpchar) OR (hdr_clm_status =
'L'::bpchar))
Total runtime: 454.60 msec
(5 rows)
Can anyone provide some clues into what is going on here? These queries
were ran within a total of 1 minute elapsed time.
Postgresql 7.3.4 on RedHat 2.1
Thanks for any insite.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: revoohc@sermonaudio.com, psql-admin@postgresql.org
Subject: Re: Index weirdness - Any ideas why
In-Reply-To: <4208E87C.3000908@sermonaudio.com>
References: <4208E87C.3000908@sermonaudio.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