Re: [HACKERS] What about LIMIT in SELECT ?

Jan Wieck wrote:
>
> Hannu Krosing wrote:
>
> > Jan Wieck wrote:
> > >    The speedup for the cursor/fetch scenario  is  so  impressive
> > >    that  I'll  create  a  post 6.4 patch. I don't want it in 6.4
> > >    because there is absolutely no query in the whole  regression
> > >    test,  where  it  suppresses  the  sort  node.
> >
> > Good, then it works as expected ;)
> >
> > More seriously, it is not within powers of current regression test
> > framework to test speed improvements (only the case where
> > performance-wise bad implementation will actually crash the backend,
> > as in the cnfify problem, but AFAIK we dont test for those now)
> >
> > >   So  we  have absolutely no check that it doesn't break anything.
> >
> > If it did pass the regression, then IMHO it did not break anything.
>
>     Thats  the  point.  The  check  if  the sort node is required
>     returns TRUE for  ALL  queries  of  the  regression.  So  the
>     behaviour when it returns FALSE is absolutely not tested.

The only way to find out is to make a new test, maybe by comparing

select * from t where key > 1 order by key;

where sort node can be dropped

and

select * from t where (key+1) > 2 order by key;

where it probably can't (I guess the optimiser is currently not smart
enough)

>     I can't call it a bugfix because it is only a performance win
>     in some situations.

In the extreme case the situation can be exhaustion of swap and disk
space resulting in a frozen computer, just trying to get 10 first rows
from a table. Its not exactly a bug, but it's not the expected
behaviour either.

>     And I feel the risk is too  high  to  put
>     untested  code  into  the  backend  at BETA2 time. The max we
>     should do is to take this one  and  the  LIMIT  thing  (maybe
>     implemented  as  I  suggested  lately),  and  put  out a Web-
>     Performance-Release at the same time we release 6.4.

Or perhaps have the patches in /contrib in 6.4 distribution
(preferrably with an option to configure to apply them ;)

-----------------
Hannu