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!