Обсуждение: Index Problem

I want to query this...
--> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; 

this query doesn't refer the index that made by this query.
--> CREATE INDEX idx_bbs ON bbs (ref, step);

but, i change the query that "ref desc" to "ref asc".
then query refer the index, and i can see a result very fast. :-(

so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index.
what do i do?
how make an index?




==================================================

==================================================
우리 인터넷, Daum
평생 쓰는 무료 E-mail 주소 한메일넷
지구촌 한글 검색서비스 Daum FIREBALL
http://www.daum.net

"Kim Yunhan" <spbear@hanmail.net> writes:
> I want to query this...
> --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; 

> this query doesn't refer the index that made by this query.
> --> CREATE INDEX idx_bbs ON bbs (ref, step);

Well, no.  The ordering the query is asking for has nothing to do with
the ordering of the index, so the index is no help.

The simplest answer would be to restructure your data so that the order
you are interested in corresponds to the natural index order.  If you
don't like that answer, you could consider making a "reverse" operator
class that sorts the datatype of "ref" in reverse order, and then
building an index on (ref reverse_ops, step).  I think the planner would
be smart enough to realize that it could use such an index for your
query ... but it's a sufficiently off-the-wall case that I doubt
anyone's ever tried it.  Lemme know if it works ;-)
        regards, tom lane