X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 751178B9F1C for ; Tue, 8 Feb 2005 16:47:14 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30238-03 for ; Tue, 8 Feb 2005 16:47:04 +0000 (GMT) Received: from hub.org (hub.org [200.46.204.220]) by svr1.postgresql.org (Postfix) with ESMTP id 2807D8B9EA4 for ; Tue, 8 Feb 2005 16:47:05 +0000 (GMT) Received: from localhost (unknown [200.46.204.144]) by hub.org (Postfix) with ESMTP id 60E7912B200 for ; Tue, 8 Feb 2005 12:47:04 -0400 (AST) Received: from hub.org ([200.46.204.220]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30014-08 for ; Tue, 8 Feb 2005 16:46:55 +0000 (GMT) Received: from ganymede.hub.org (blk-224-186-245.eastlink.ca [24.224.186.245]) by hub.org (Postfix) with ESMTP id AA6EA12B13D for ; Tue, 8 Feb 2005 12:46:54 -0400 (AST) Received: by ganymede.hub.org (Postfix, from userid 1000) id C5A90383D8; Tue, 8 Feb 2005 12:46:53 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id C4498383D3 for ; Tue, 8 Feb 2005 12:46:53 -0400 (AST) X-Return-Path: X-Received: from localhost ([unix socket]) by ganymede.hub.org (Cyrus v2.2.10) with LMTPA; Tue, 08 Feb 2005 12:31:58 -0400 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id DCE2C37B13 for ; Tue, 8 Feb 2005 12:30:48 -0400 (AST) X-Received: from mail.postgresql.org [200.46.204.71] by localhost with IMAP (fetchmail-6.2.5) for scrappy@localhost (single-drop); Tue, 08 Feb 2005 12:30:48 -0400 (AST) X-Received: from svr1.postgresql.org ([unix socket]) by svr1.postgresql.org (Cyrus v2.2.10) with LMTPA; Tue, 08 Feb 2005 16:27:00 +0000 X-Sieve: CMU Sieve 2.2 X-Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E301D8B9EF6 for ; Tue, 8 Feb 2005 16:26:59 +0000 (GMT) X-Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28191-08 for ; Tue, 8 Feb 2005 16:26:50 +0000 (GMT) X-Received: from mail.astudios.com (ns1.astudios.com [66.49.77.3]) by svr1.postgresql.org (Postfix) with ESMTP id 7CB598B9F02 for ; Tue, 8 Feb 2005 16:26:49 +0000 (GMT) thread-index: AcUN+y3RsxiCmUYrQDSq2/rJ1C2lhw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181 Content-Class: urn:content-classes:message Importance: normal X-Received: from [10.4.13.50] ([66.194.26.150]) by mail.astudios.com with Microsoft SMTPSVC(6.0.3790.211); Tue, 8 Feb 2005 11:28:10 -0500 Message-ID: <4208E87C.3000908@sermonaudio.com> Date: Tue, 08 Feb 2005 11:27:40 -0500 From: "Chris Hoover" User-Agent: Mozilla Thunderbird 1.0 (X11/20041227) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Cc: Subject: Index weirdness - Any ideas why X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; format=flowed; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable X-OriginalArrivalTime: 08 Feb 2005 16:28:10.0500 (UTC) FILETIME=[2DB95440:01C50DFB] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-DCC: : X-Spam-Pyzor: ReSent-Date: Tue, 8 Feb 2005 12:46:49 -0400 (AST) Resent-From: "Marc G. Fournier" Resent-To: pgsql-admin@postgresql.org ReSent-Subject: Index weirdness - Any ideas why ReSent-Message-ID: <20050208124649.R94338@ganymede.hub.org> X-Virus-Scanned: by amavisd-new at hub.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.498 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/71 X-Sequence-Number: 16238 We are seeing some strangeness with our indexes. Yesterday, one of our=20 tell tale queries was performing very badly. We tried to analyze the=20 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=20 would perform very sporadically. Some times it would run sub second,=20 and then the next run (seconds apart) would run for several seconds.=20 Has anyone ever seen anything like this? Here are some explain analyzes to for detail: explain analyze select count(1) from where hdr_user_id=3D'username' and hdr_clm_status in ('H','E','A','R','T','V','P','L'); QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------------------- Aggregate (cost=3D11927.83..11927.83 rows=3D1 width=3D0) (actual=20 time=3D2191.73..2191.73 rows=3D1 loops=3D1) -> Index Scan using
_hdr_user_id_idx on
=20 (cost=3D0.00..11926.53 rows=3D521 width=3D0) (actual = time=3D0.31..2190.98=20 rows=3D380 loops=3D1) Index Cond: (hdr_user_id =3D 'username'::character varying) Filter: ((hdr_clm_status =3D 'H'::bpchar) OR (hdr_clm_status = =3D=20 'E'::bpchar) OR (hdr_clm_status =3D 'A'::bpchar) OR (hdr_clm_status=20 =3D'R'::bpchar) OR (hdr_clm_status =3D 'T'::bpchar) OR (hdr_clm_status = =3D=20 'V'::bpchar) OR (hdr_clm_status =3D 'P'::bpchar) OR (hdr_clm_status =3D=20 'L'::bpchar)) Total runtime:2191.84 msec (5 rows) QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------------------- Aggregate (cost=3D11927.83..11927.83 rows=3D1 width=3D0) (actual=20 time=3D863.54..863.54 rows=3D1 loops=3D1) -> Index Scan using
_hdr_user_id_idx on
=20 (cost=3D0.00..11926.53 rows=3D521 width=3D0) (actual time=3D0.14..863.22 = rows=3D380 loops=3D1) Index Cond: (hdr_user_id =3D 'username'::character varying) Filter: ((hdr_clm_status =3D 'H'::bpchar) OR (hdr_clm_status = =3D=20 'E'::bpchar) OR (hdr_clm_status =3D 'A'::bpchar) OR (hdr_clm_status=20 =3D'R'::bpchar) OR (hdr_clm_status =3D 'T'::bpchar) OR (hdr_clm_status = =3D=20 'V'::bpchar) OR (hdr_clm_status =3D 'P'::bpchar) OR (hdr_clm_status =3D=20 'L'::bpchar)) Total runtime: 863.61 msec (5 rows) QUERY PLAN -------------------------------------------------------------------------= -------------------------------------------------------------------------= -------------------------------------------------------------------------= --------------------------------------------------------------------- Aggregate (cost=3D11927.83..11927.83 rows=3D1 width=3D0) (actual=20 time=3D454.50..454.50 rows=3D1 loops=3D1) -> Index Scan using
_hdr_user_id_idx on
=20 (cost=3D0.00..11926.53 rows=3D521 width=3D0) (actual time=3D0.27..453.93 = rows=3D380 loops=3D1) Index Cond: (hdr_user_id =3D 'username'::character varying) Filter: ((hdr_clm_status =3D 'H'::bpchar) OR (hdr_clm_status = =3D=20 'E'::bpchar) OR (hdr_clm_status =3D 'A'::bpchar) OR (hdr_clm_status=20 =3D'R'::bpchar) OR (hdr_clm_status =3D 'T'::bpchar) OR (hdr_clm_status = =3D=20 'V'::bpchar) OR (hdr_clm_status =3D 'P'::bpchar) OR (hdr_clm_status =3D=20 '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.