I have a question about using index in order statement.

From: "kevin" <kevin(at)mail(dot)kinew(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: I have a question about using index in order statement.
Date: 2007-11-02 04:14:01
Message-ID: 000601c81d06$ce7d8140$100aa8c0@lion
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Question:
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
order by code_ desc

Postgresql response
Sort (cost=100001815.08..100001852.56 rows=14990 width=56)
Sort Key: code_
-> Seq Scan on a_test (cost=100000000.00..100000260.90 rows=14990 width=56)

ix_3 condition :
When I try

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

Postgresql response
Index Scan using ix_3 on a_test (cost=0.00..769.27 rows=14990 width=18)

Table schema :

CREATE TABLE a_test
(
t_key_ bigint NOT NULL,
code_ character varying(15)
)
WITH (OIDS=TRUE);
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);

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andy.Xue 2007-11-02 10:01:57 Postgresql Domain Names
Previous Message Tom Lane 2007-11-01 22:32:57 Re: PostgreSQL crash on Freebsd 7