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);
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 |