question about alternate ordering of results

Started by Nonameabout 14 years ago4 messagesgeneral
Jump to latest
#1Noname
hamann.w@t-online.de

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Noname (#1)
Re: question about alternate ordering of results

hamann.w@t-online.de writes:

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?

Yes. It sounds like you have pretty much all the spare parts you need,
you just have to collect them together into an opclass for each
ordering you want.

Is this relevant to performance?

Somewhat, in that it helps the planner optimize ordering considerations.
But IIRC the main argument for tightening it up was to catch mistakes
wherein somebody says "ORDER BY x USING &&", or some other operator that
doesn't produce a consistent sort order.

regards, tom lane

#3Noname
hamann.w@t-online.de
In reply to: Tom Lane (#2)
Re: question about alternate ordering of results

Hi Tom,

declaring another operator class helped. At first, however,
results were sorted deifferent than expected. A little gdb session revealed that
if fact only the FUNCTION 1 entry in the operator class is used

Regards
Wolfgang Hamann

Show quoted text

hamann.w@t-online.de writes:

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?

Yes. It sounds like you have pretty much all the spare parts you need,
you just have to collect them together into an opclass for each
ordering you want.

Is this relevant to performance?

Somewhat, in that it helps the planner optimize ordering considerations.
But IIRC the main argument for tightening it up was to catch mistakes
wherein somebody says "ORDER BY x USING &&", or some other operator that
doesn't produce a consistent sort order.

regards, tom lane

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: Noname (#3)
Re: question about alternate ordering of results

hamann.w@t-online.de writes:

declaring another operator class helped. At first, however,
results were sorted deifferent than expected. A little gdb session revealed that
if fact only the FUNCTION 1 entry in the operator class is used

Well, yeah, the function had better match the operators.

regards, tom lane