X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2EF5C3ABE78 for ; Wed, 22 Dec 2004 20:09:22 +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 19581-03 for ; Wed, 22 Dec 2004 20:09:04 +0000 (GMT) Received: from web13124.mail.yahoo.com (web13124.mail.yahoo.com [216.136.174.142]) by svr1.postgresql.org (Postfix) with SMTP id D05283ABD3D for ; Wed, 22 Dec 2004 20:09:04 +0000 (GMT) Received: (qmail 94017 invoked by uid 60001); 22 Dec 2004 20:09:04 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=u4mO870/rV1DLJSQMukwMf6F322/j++cmHSlgzJUuafzo8VumEZwXtwVX7dxjGkjdRizrgETXDskJwpywyINgZpk0ARaOxjazEkbHwy2X1HeqcSG2StWMsJEqAus6XW185CGPJTqnjrh3ApcJ/iQ2GJlMMtpKMgy0q81ETTI0ag= ; Message-ID: <20041222200904.94015.qmail@web13124.mail.yahoo.com> Received: from [63.78.249.230] by web13124.mail.yahoo.com via HTTP; Wed, 22 Dec 2004 12:09:04 PST Date: Wed, 22 Dec 2004 12:09:04 -0800 (PST) From: Litao Wu Subject: Why so much time difference with a same query/plan? To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.374 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200412/271 X-Sequence-Number: 9646 Merry Xmas! I have a query. It sometimes runs OK and sometimes horrible. Here is result from explain analyze: explain analyze SELECT module, sum(c1) + sum(c2) + sum(c3) + sum(c4) + sum(c5) AS "count" FROM xxx WHERE created >= ('now'::timestamptz - '1 day'::interval) AND customer_id='158' AND domain='xyz.com' GROUP BY module; There is an index: Indexes: xxx_idx btree (customer_id, created, "domain") Table are regularlly "vacuum full" and reindex and it has 3 million rows. QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=139.53..141.65 rows=12 width=30) (actual time=17623.65..17623.65 rows=0 loops=1) -> Group (cost=139.53..140.14 rows=121 width=30) (actual time=17623.64..17623.64 rows=0 loops=1) -> Sort (cost=139.53..139.83 rows=121 width=30) (actual time=17623.63..17623.63 rows=0 loops=1) Sort Key: module -> Index Scan using xxx_idx on xxx (cost=0.00..135.33 rows=121 width=30) (actual time=17622.95..17622.95 rows=0 loops=1) Index Cond: ((customer_id = 158) AND (created >= '2004-12-02 11:26:22.596656-05'::timestamp with time zone) AND ("domain" = 'xyz.com'::character varying)) Total runtime: 17624.05 msec (7 rows) QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=142.05..144.21 rows=12 width=30) (actual time=1314931.09..1314931.09 rows=0 loops=1) -> Group (cost=142.05..142.66 rows=124 width=30) (actual time=1314931.08..1314931.08 rows=0 loops=1) -> Sort (cost=142.05..142.36 rows=124 width=30) (actual time=1314931.08..1314931.08 rows=0 loops=1) Sort Key: module -> Index Scan using xxx_idx on xxx (cost=0.00..137.74 rows=124 width=30) (actual time=1314930.72..1314930.72 rows=0 loops=1) Index Cond: ((customer_id = 158) AND (created >= '2004-12-01 15:21:51.785526-05'::timestamp with time zone) AND ("domain" = 'xyz.com'::character varying)) Total runtime: 1314933.16 msec (7 rows) What can I try? Thanks, __________________________________ Do you Yahoo!? Dress up your holiday email, Hollywood style. Learn more. http://celebrity.mail.yahoo.com