Thread: indexes problem

Hi all. I have a problem with two tables of same structure: 'orders_stat_pre' and 'orders_stat_pre_new'.
store=# \d orders_stat_pre
 Column         | Type                       | Modifiers
----------------+----------------------------+-----------
 id             | integer                    | not null
 user_name      | text                       |
 category_name  | character varying(10)      |
 ctime          | timestamp without timezone |
Indexes:
    "orders_stat_pre_pkey" PRIMARY KEY, btree (id)
    "orders_stat_pre_user_idx" btree (user_name, category_name, ctime DESC)


store=# \d orders_stat_pre_new
 Column         | Type                       | Modifiers
----------------+----------------------------+-----------
 id             | integer                    |
 user_name      | text                       |
 category_name  | character varying(10)      |
 ctime          | timestamp without timezone |
Indexes:
    "orders_stat_pre_new_user_idx" btree (user_name, category_name, ctime DESC)

I try to select last 10 orders from old table (i.e. 'orders_stat_pre'):

store=# explain select * from orders_stat_pre where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10;
                          QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..40.40 rows=10 width=335)                                                     
   ->  Index Scan using orders_stat_pre_user_idx on orders_stat_pre  (cost=0.00..15505.87 rows=3838 width=335)       
         Index Cond: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text))
(3 rows)                                                                                         

Then I do the same query on new table (i.e. 'orders_stat_pre_new'):

store=# explain select * from orders_stat_pre_new where user_name = 'Alex' and category_name = 'Books' order by ctime desc limit 10;
                          QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit  (cost=1719969.83..1719969.86 rows=10 width=563)                                        
   ->  Sort  (cost=1719969.83..1719981.08 rows=4499 width=563)                                 
         Sort Key: ctime
         ->  Seq Scan on orders_stat_pre_new  (cost=0.00..1719872.61 rows=4499 width=563)                   
               Filter: ((user_name = 'Alex'::text) AND ((category_name)::text = 'Books'::text))
(5 rows)                                                                                       

I'm confused on how can I optimize the last query? Or where I can find corresponding info. Thank you!

On 1/27/2010 7:32 AM, J Scanf wrote:
> Hi all. I have a problem with two tables of same structure:
> 'orders_stat_pre' and 'orders_stat_pre_new'.
> store=# \d orders_stat_pre
>   Column         | Type                       | Modifiers
> ----------------+----------------------------+-----------
>   id             | integer                    | not null
>   user_name      | text                       |
>   category_name  | character varying(10)      |
>   ctime          | timestamp without timezone |
> Indexes:
> "orders_stat_pre_pkey" PRIMARY KEY, btree (id)
> "orders_stat_pre_user_idx" btree (user_name, category_name, ctime DESC)
>
>
> store=# \d orders_stat_pre_new
>   Column         | Type                       | Modifiers
> ----------------+----------------------------+-----------
>   id             | integer                    |
>   user_name      | text                       |
>   category_name  | character varying(10)      |
>   ctime          | timestamp without timezone |
> Indexes:
> "orders_stat_pre_new_user_idx" btree (user_name, category_name, ctime DESC)
>
> I try to select last 10 orders from old table (i.e. 'orders_stat_pre'):
>
> store=# explain select * from orders_stat_pre where user_name = 'Alex'
> and category_name = 'Books' order by ctime desc limit 10;
>                            QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------
>   Limit  (cost=0.00..40.40 rows=10 width=335)
>     ->  Index Scan using orders_stat_pre_user_idx on orders_stat_pre
> (cost=0.00..15505.87 rows=3838 width=335)
>           Index Cond: ((user_name = 'Alex'::text) AND
> ((category_name)::text = 'Books'::text))
> (3 rows)
>
> Then I do the same query on new table (i.e. 'orders_stat_pre_new'):
>
> store=# explain select * from orders_stat_pre_new where user_name =
> 'Alex' and category_name = 'Books' order by ctime desc limit 10;
>                            QUERY PLAN
> ------------------------------------------------------------------------------------------------
>   Limit  (cost=1719969.83..1719969.86 rows=10 width=563)
>     ->  Sort  (cost=1719969.83..1719981.08 rows=4499 width=563)
>           Sort Key: ctime
>           ->  Seq Scan on orders_stat_pre_new  (cost=0.00..1719872.61
> rows=4499 width=563)
>                 Filter: ((user_name = 'Alex'::text) AND
> ((category_name)::text = 'Books'::text))
> (5 rows)
>
> I'm confused on how can I optimize the last query? Or where I can find
> corresponding info. Thank you!
>

1) an "explain analyze" might give more useful info.
2) are your stats up do date?  run 'analyze orders_stat_pre_new' and try
again
3) you might be indexing too much.  An index on just user_name might be
enough.  The extra fields (category_name and ctime) may not help you as
much as you think it will.  A more complicated index (multiple fields)
makes it harder for PG to use.  Drop that index and create one on just
user_name and compare the times.

-Andy