From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kim Yunhan <spbear@hanmail.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index Problem
Date: Fri, 09 Feb 2001 01:22:54 -0500
Message-ID: <17660.981699774@sss.pgh.pa.us> (raw)
In-Reply-To: <20010209145408.HM.40000000001Vhp4@www5.hanmail.net>
References: <20010209145408.HM.40000000001Vhp4@www5.hanmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, spbear@hanmail.net
Subject: Re: Index Problem
In-Reply-To: <17660.981699774@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox