Interesting Orders in the Order by/Group by Clauses

[Kevin Wilfong]

In class yesterday we said that given an example like the following (I
don't remember it exactly)

select *
from emp e, manages m, dept d
where e.empno = m.empno
and d.deptno = m.deptno
order by e.empname and m.empno

then the interesting order from the order by clause was ((e.empname),
(e.empno, m.empno))

and ((e.empname)) alone is not an interesting order.

However, what if it was something like

select *
from emp e, manages m, dept d
where e.empno = m.empno
and d.deptno = m.deptno
order by e.empname and d.deptname

in which case the pathkey representing the order by clause would look
like ((e.empname), (d.deptname))

In this case, this order would not be applicable until higher up in the
plan tree, so would ((e.empname)) and/or ((d.deptname)) be considered
interesting orders for the purposes of this assignment?

[Ken Salem]

I'm not sure I understand this question. You can write

"order by e.empname, e.deptname"

which means that the output should be sorted with empname as the
primary sort key and deptname as the secondary sort key.

Neither emp or dept alone can produce this order. Since you are
only producing new paths for single-relation queries, you would not
need to add any additional paths to account for the order by clause
in your second example.

Alternatively, you may elect to generate extra paths corresponding
to any *prefix* of the compound sort or group by key - in your example,
that would mean a sort on e.empname, which could be produced for the
emp table. I do not think that Postgres will actually be able to
take advantage of such a partial sort or grouping further downstream
in the plan, but in principle it is possible to do so - we discussed
this a bit in class last time.
Generating these extra prefix sort orders is optional for this
assignment - you may do so, but you are not required to.

-KMS

Post by Kevin Wilfong
In class yesterday we said that given an example like the following (I
don't remember it exactly)
select *
from emp e, manages m, dept d
where e.empno = m.empno
and d.deptno = m.deptno
order by e.empname and m.empno
then the interesting order from the order by clause was ((e.empname),
(e.empno, m.empno))
and ((e.empname)) alone is not an interesting order.
However, what if it was something like
select *
from emp e, manages m, dept d
where e.empno = m.empno
and d.deptno = m.deptno
order by e.empname and d.deptname
in which case the pathkey representing the order by clause would look
like ((e.empname), (d.deptname))
In this case, this order would not be applicable until higher up in the
plan tree, so would ((e.empname)) and/or ((d.deptname)) be considered
interesting orders for the purposes of this assignment?