pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Litao Wu <litaowu@yahoo.com>
To: Litao Wu <litaowu@yahoo.com>
To: pgsql-performance@postgresql.org
Subject: Re: Why so much time difference with a same query/plan?
Date: Wed, 22 Dec 2004 13:52:40 -0800 (PST)
Message-ID: <20041222215240.12345.qmail@web13124.mail.yahoo.com> (raw)
In-Reply-To: <20041222200904.94015.qmail@web13124.mail.yahoo.com>

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 <litaowu@yahoo.com> 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



view thread (5+ messages)  latest in thread

Message-ID: <20041222215240.12345.qmail@web13124.mail.yahoo.com>
Permalink:  ../20041222215240.12345.qmail@web13124.mail.yahoo.com/
Also on:    postgresql.org/message-id/20041222215240.12345.qmail@web13124.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: litaowu@yahoo.com
  Subject: Re: Why so much time difference with a same query/plan?
  In-Reply-To: <20041222215240.12345.qmail@web13124.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox