From: | J Scanf <jscanf(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | indexes problem |
Date: | 2010-01-27 13:32:52 |
Message-ID: | d70bfe901001270532i464b7300nd72b16d771dfc53a@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-01-27 15:35:25 | Re: Variadic polymorpic functions |
Previous Message | hubert depesz lubaczewski | 2010-01-27 13:10:13 | Re: 100% of CPU utilization postgres process |