On 05/13/24 09:35, aa wrote:
> If you call the action of "sifting" ordering, then yes. If you don't call
> it ordering, then no.
One thing seems intriguing about this idea: normally, an expected
property of any ORDER BY is that no result row can be passed down
the pipe until all input rows have been seen.
In the case of ORDER BY <boolean expression>, or more generally
ORDER BY <expression type with small discrete value space>, a
pigeonhole sort could be used—and rows mapping to the ordered-first
pigeonhole could be passed down the pipe on sight. (Rows mapping to
any later pigeonhole still have to be held to the end, unless some
further analysis can identify when all rows for earlier pigeonholes
must have been seen).
I don't know whether any such ORDER BY strategy is already implemented,
or would be useful enough to be worth implementing, but it might be
handy in cases where a large number of rows are expected to map to
the first pigeonhole. Intermediate storage wouldn't be needed for those,
and some follow-on processing could go on concurrently.
The usage example offered here ("sift" nulls last, followed by
a LIMIT) does look a lot like a job for a WHERE clause though.
Regards,
-Chap