From: | Jonathan Tse <dev(at)tsez(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Order by and index |
Date: | 2005-10-05 06:52:46 |
Message-ID: | 4343783E.5030608@tsez.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Dear all,
I am a new user of postgresql and got a question of using index.
I have a table with the folloing definition
CREATE TABLE t_post
(
post_id serial NOT NULL,
ownerid int4,
t_stamp int4,
CONSTRAINT t_post_pkey PRIMARY KEY (post_id)
)
WITH OIDS;
When I create an index for ownerid and t_stamp
CREATE INDEX ownerid_ts_idx
ON t_post
USING btree
(ownerid, t_stamp);
and execute explain to this query:
select * from t_post where ownerid = 2 and t_stamp = 1128487197
the query plan said the following :
Index Scan using ownerid_ts_idx on t_post (cost=0.00..17.97 rows=4
width=463)
Index Cond: ((ownerid = 4) AND (t_stamp = 1128487197))
It is good. However, if I execute the following query:
select * from t_post where ownerid = 2
order by t_stamp
Sort (cost=2998.31..3001.79 rows=1392 width=463)
Sort Key: t_stamp
-> Seq Scan on t_post (cost=0.00..2925.62 rows=1392 width=463)
Filter: (ownerid = 4)
The query planner doesn't use the index for sorting. Is it normal
and is there any index strategy that I can employ to optimize this
query? Thanks a lot.
Best regards,
Jonathan Tse
From | Date | Subject | |
---|---|---|---|
Next Message | Manish Raj Sharma | 2005-10-05 12:15:58 | display table contents using a stored proc |
Previous Message | Alvaro Cobo | 2005-10-05 04:14:10 | Re: Moving from MySQL |