Why so much time difference with a same query/plan? - Mailing list pgsql-performance

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