Обсуждение: 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.50rows=10000 width=8) (1 row)
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
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@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.
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.
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).
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