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