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 [EMAIL PROTECTED])