To: "Kim Yunhan" <spbear@hanmail.net>
cc: pgsql-sql@postgresql.org
Subject: Re: Index Problem 
In-reply-to: <20010209145408.HM.40000000001Vhp4@www5.hanmail.net> 
References: <20010209145408.HM.40000000001Vhp4@www5.hanmail.net>
Comments: In-reply-to "Kim Yunhan" <spbear@hanmail.net>
	message dated "Fri, 09 Feb 2001 14:54:08 +0700"
Date: Fri, 09 Feb 2001 01:22:54 -0500
Message-ID: <17660.981699774@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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