Thread

1. index suggestion for 7.4

   Bruno Wolff III <bruno@wolff.to> — 2003-05-30T16:02:00Z

   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)
   
   

2. Re: index suggestion for 7.4

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-30T17:32:39Z

   Bruno Wolff III <bruno@wolff.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
   
   

3. Re: index suggestion for 7.4

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-30T17:42:24Z

   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.
   
   
   
   

4. Re: index suggestion for 7.4

   Bruno Wolff III <bruno@wolff.to> — 2003-05-30T18:02:13Z

   On Fri, May 30, 2003 at 10:42:24 -0700,
     Stephan Szabo <sszabo@megazone23.bigpanda.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.
   
   

5. Re: index suggestion for 7.4

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-30T18:31:23Z

   On Fri, 30 May 2003, Bruno Wolff III wrote:
   
   > On Fri, May 30, 2003 at 10:42:24 -0700,
   >   Stephan Szabo <sszabo@megazone23.bigpanda.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.
   
   
   

6. Re: index suggestion for 7.4

   Bruno Wolff III <bruno@wolff.to> — 2003-05-30T19:03:17Z

   On Fri, May 30, 2003 at 11:31:23 -0700,
     Stephan Szabo <sszabo@megazone23.bigpanda.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).
   
   

7. Re: index suggestion for 7.4

   Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-30T19:54:52Z

   Bruno Wolff III <bruno@wolff.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