question about alternate ordering of results

From: hamann(dot)w(at)t-online(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: question about alternate ordering of results
Date: 2012-04-06 08:41:26
Message-ID: wolfgang-1120406104126.A0230683@amadeus3.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Maxim Boguk 2012-04-06 09:46:48 Postgresql 9.0.7 weird planner decision (rows in plan close to reality but plan suboptimal)
Previous Message Sébastien Lardière 2012-04-06 07:53:04 Re: BD impossible à recharger