Order by and index
От | Jonathan Tse |
---|---|
Тема | Order by and index |
Дата | |
Msg-id | 4343783E.5030608@tsez.net обсуждение исходный текст |
Ответы |
Re: Order by and index
|
Список | 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
В списке pgsql-novice по дате отправления: