Order by and index

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

Responses

Browse pgsql-novice by date

  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