On 5 Feb 2003, Greg Stark wrote:
>
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>
> > > This would have a couple big advantages:
> > >
> > > 1) If columns were excluded from the results from limit/offset clauses then
> > > possibly expensive functions in the select list wouldn't have to be
> > > calculated. It also means if the sort is quick but the functions slow that
> > > the first rows would be returned quickly to the application even if the
> > > total time was the same.
> >
> > That's a useful advantage. You can probably get this effect from pushing
> > clauses into from list subselects as a current optimization, but it'd be
> > nice since that's not an obvious conversion.
>
> Not if the clause depends on the sort results such as limit/offset or having
> clauses.
Sorry, I thought we were speaking specifically of the
select expensive_function(...), col1, ... from table1, ... order by col1,
... limit ... case which probably safely can be written into
select expensive_function(...), col1, ... from (select col1, ... from
table1, ... order by col1, ... limit ...) foo;
> > > 2) The sort could be done on pointers to the tuples rather than pushing the
> > > data in the tuple around in the sort. Obviously the transaction integrity
> > > issues are tricky with this though. But it could save a lot of memory
> > > pressure from sorts.
> >
> > This'd be an advantage in the case of a single table. Unless you can
> > guarantee the join keeps the ordering properties, I don't think this'd
> > help with anything that's doing joins or anything more complicated.
>
> Joins shouldn't be a problem, just sort a list of pointers to records and have
> a structure on the side saying which tables each pointer corresponds to and
> which columns and expressions are requested from each. I'm not sure how to
> retain transactional integrity with this though.
Hmm, are you talking about pointers to the original tables or pointers to
the output of the last step? The latter should be fine, but the former is
problematic I'd think.