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 5CECA3ABE32 for ; Wed, 22 Dec 2004 21:52:48 +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 54451-09 for ; Wed, 22 Dec 2004 21:52:39 +0000 (GMT) Received: from web13124.mail.yahoo.com (web13124.mail.yahoo.com [216.136.174.142]) by svr1.postgresql.org (Postfix) with SMTP id 0E56E3ABC1C for ; Wed, 22 Dec 2004 21:52:40 +0000 (GMT) Received: (qmail 12347 invoked by uid 60001); 22 Dec 2004 21:52:40 -0000 Comment: DomainKeys? See http://antispam.yahoo.com/domainkeys DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; b=BqAgjs0/WRL1mMlMo8sXGu23UKFEOQcPzYEnikNHoDaZ2mpH35rHtNg+ONQI8gJl6RWN/HI8QFH+Y16DEeCwe/antjRGzKdWNRSGQWa5XgYV0rBLGmJoZO0ASl8v8jEbEeDMfwkWeEg6ovelfRp8s5Lmtdq733BvtKDcPaySkNc= ; Message-ID: <20041222215240.12345.qmail@web13124.mail.yahoo.com> Received: from [63.78.249.230] by web13124.mail.yahoo.com via HTTP; Wed, 22 Dec 2004 13:52:40 PST Date: Wed, 22 Dec 2004 13:52:40 -0800 (PST) From: Litao Wu Subject: Re: Why so much time difference with a same query/plan? To: Litao Wu , pgsql-performance@postgresql.org In-Reply-To: <20041222200904.94015.qmail@web13124.mail.yahoo.com> 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.28 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200412/274 X-Sequence-Number: 9649 Does the order of columns in the index matter since more than 50% customer_id = 158? I think it does not in Oracle. Will the performance be better if I change index xxx_idx to ("domain", customer_id, created)? I will test myself when possible. Thanks, --- Litao Wu wrote: > 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 > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250