question about alternate ordering of results

Поиск
Список
Период
Сортировка
От hamann.w@t-online.de
Тема question about alternate ordering of results
Дата
Msg-id wolfgang-1120406104126.A0230683@amadeus3.local
обсуждение исходный текст
Ответы Re: question about alternate ordering of results
Список pgsql-general
Hi,

in the old days (version 7.x) I created a set of btree operators

create operator <& (
  procedure = mytext_lt,
  leftarg = text,
  rightarg = text,
  commutator = >&,
  negator = >=&,
  restrict = scalarltsel,
  join = scalarltjoinsel
);

etc. for a completeoperator class

create operator class mytext_ops
  for type text using btree as
     operator 1 <& (text,text),
....
);

Some tables have an index using this operator class
create index blah_t  on blah using btree (col1 mytext_ops);

I also created a few extra operators
create operator <&- (
  procedure = mytext_lt_x,
  leftarg = text,
  rightarg = text,
  commutator = >&-,
  negator = >=&-,
  restrict = scalarltsel,
  join = scalarltjoinsel
);
and could use them in a query, like

select .... from blah order by col1;
select .... from blah order by col1 using <&;
select .... from blah order by col1 using <&-;
(resulting in 3 different result orderings)

BTW: the difference between the two ops is "natural" ordering of numerics: if two
strings start to differ at "3rd" vs "17th", the <& returns them in alpha sort and the <&-
produces proper numeric order

Now, in versions 8 and later the "using <&-" is rejected,
the ordering op "needs to be < or > member of a btree operator class".
What is needed to create the old behaviour again
- create a complete operator class, including new names for the unchanged equals/not equals function?
- adding another index to get the feature

Is this relevant to performance? I guess that an index using one opclass and ordering using the
other one would result in an index scan to locate the data and then an extra ordering pass to
order them the other way

Regards
Wolfgang Hamann





В списке pgsql-general по дате отправления:

Предыдущее
От: "Greg Sabino Mullane"
Дата:
Сообщение: Re: Using DEFAULT as a parameter value with PQexecPrepare()
Следующее
От: Maxim Boguk
Дата:
Сообщение: Postgresql 9.0.7 weird planner decision (rows in plan close to reality but plan suboptimal)