I have a question about using index in order statement.

Поиск
Список
Период
Сортировка
От kevin
Тема I have a question about using index in order statement.
Дата
Msg-id 000601c81d06$ce7d8140$100aa8c0@lion
обсуждение исходный текст
Ответы Re: I have a question about using index in order statement.  (Heikki Linnakangas <heikki@enterprisedb.com>)
Список pgsql-bugs
Question:=20
I have a question about using index in order statement.
Why index ix_2 work by Seq Scan and index ix_3 work by Index Scan.

Example :

ix_2 condition :
When I try

  explain
  select * from a_test=20
  order by code_ desc

Postgresql response=20
  Sort  (cost=3D100001815.08..100001852.56 rows=3D14990 width=3D56)
    Sort Key: code_
    ->  Seq Scan on a_test  (cost=3D100000000.00..100000260.90 rows=3D14990=
 width=3D56)

ix_3 condition :
When I try

  explain
  select * from a_test=20
  order by lower(code_) desc

Postgresql response=20
    Index Scan using ix_3 on a_test  (cost=3D0.00..769.27 rows=3D14990 widt=
h=3D18)
=20=20=20=20

Table schema :

CREATE TABLE a_test
(
  t_key_ bigint NOT NULL,
  code_ character varying(15)
)
WITH (OIDS=3DTRUE);
ALTER TABLE a_test OWNER TO postgres;

CREATE INDEX ix_2
  ON a_test
  USING btree
  (code_ DESC);

CREATE INDEX ix_3
  ON a_test
  USING btree
  (lower(code_::text) DESC);

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL crash on Freebsd 7
Следующее
От: "Andy.Xue"
Дата:
Сообщение: Postgresql Domain Names