index suggestion for 7.4

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.

For example:

bruno=> \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
col1 | integer |
col2 | integer |
Indexes:
"test1" btree (col1, ((- col2)))

bruno=> explain select col1, col2 from test order by col1 asc, col2 desc;
QUERY PLAN
----------------------------------------------------------------
Sort (cost=814.39..839.39 rows=10000 width=8)
Sort Key: col1, col2
-> Seq Scan on test (cost=0.00..150.00 rows=10000 width=8)
(3 rows)

bruno=> explain select col1, col2 from test order by col1 asc, -col2 asc;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using test1 on test (cost=0.00..337.50 rows=10000 width=8)
(1 row)

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> 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 -)

I don't think it should; that's an extremely datatype-dependent bit of
analysis, and the planner does not have any means of ascertaining
whether the equivalency holds for a particular "-" operator and index
opclass.

The correct way to set up this sort of thing would be to build a
"backwards ordering" operator class, not to use an index on "-col".

regards, tom lane

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.

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.

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

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.

On Fri, May 30, 2003 at 11:31:23 -0700,
Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:
> On Fri, 30 May 2003, Bruno Wolff III wrote:
>
> > 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.

I went back and reread the stuff on NEGATOR and found it only applies
to operators that return boolean types. I had thought it was different
and would let you make the deduction a > b <=> -a <= -b, but that isn't
the case. Instead it lets you make the deduction that a > b <=> NOT (a <= b).

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> I went back and reread the stuff on NEGATOR and found it only applies
> to operators that return boolean types. I had thought it was different
> and would let you make the deduction a > b <=> -a <= -b, but that isn't
> the case. Instead it lets you make the deduction that a > b <=> NOT (a <= b).

Right, the reason NEGATOR exists is to let prepqual.c flatten out NOTs
where possible (this is the same part of the code that applies
DeMorgan's Laws and other boolean algebra to try to bring a qual
condition into the simplest possible form).

To do something useful with "-" and descending order, we'd need some way
of explicitly associating "-" operators with btree opclasses. I'm not
convinced that it's worth the trouble, especially when it'd really only
apply to the numeric datatypes ("-" on text is a pretty unappealing
concept...). Stephan's suggestion of providing standard reverse-order
opclasses seems more attractive to me. Even if people didn't want to
put them into the mainstream, they could be consed up as a contrib
module with not a lot of effort.

regards, tom lane