Re: index suggestion for 7.4

On Fri, 30 May 2003, Bruno Wolff III wrote:

> On Fri, May 30, 2003 at 10:42:24 -0700,
> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
> > On Fri, 30 May 2003, Bruno Wolff III wrote:
> >
> > > Now that expressions can be used in indexes in 7.4 you can have multicolumn
> > > indexes that are ordered in different directions. However the planner
> > > doesn't seem to understand that order by -col asc is the same as order by
> > > col desc (for at least the normal -) so you have to be careful how you
> > > write queries when doing this.
> >
> > I think it'd be better to make it easier to make indexes where some
> > columns are reversed. I'm not sure that making a reverse opclass for
> > btree (one that goes >, >=, =, <=, < I guess) is a complete solution
> > even for btree but if it is, we could provide them. I think this would
> > also have the advantage of not requiring wacky queries to use the index
> > for multicolumn lookups as well.
>
> I was hoping the new stuff Tom added would make doing this easier. The issue
> has come up before and at least at that time it didn't get changed so I
> expected it wasn't easy to do.
>
> I thought maybe there was information for the - operator
> that would allow you to know that you could use an index on -col
> to go in the reverse direction safely.

Not really. I think that if you were to do that, you'd probably need to
provide an additional thing to the opclass to let it know. Otherwise it'd
be unsafe for user defined types/user defined - operators and doesn't help
on things where - isn't the correct way to do it.

> The new stuff still is easier to use then creating a new opclass which was
> the old solution.

It might make sense to provide descending opclasses as part of the base
install, <type>_desc_ops or something for the types that have btree
opclasses.