Order by and index

Поиск
Список
Период
Сортировка
От Jonathan Tse
Тема Order by and index
Дата
Msg-id 4343783E.5030608@tsez.net
обсуждение исходный текст
Ответы Re: Order by and index  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: andrew@borley-hall.eclipse.co.uk
Дата:
Сообщение: grant select on all tables
Следующее
От: Manish Raj Sharma
Дата:
Сообщение: display table contents using a stored proc