Re: Select subset of rows

On Sun, 27 Aug 2000, John McKown wrote:

> On Sun, 27 Aug 2000, Stephan Szabo wrote:
>
> >
> > Of course immediately after sending the last message and logging off my
> > ISP I figured out the simpler way for the third one:
> >
> > begin;
> > select salary into temp saltemp from employee order by salary desc
> > limit 5;
> > select name from employee where exists (select * from saltemp where
> > saltemp.salary=employee.salary);
> > end;
> >
> > Stephan Szabo
> > sszabo(at)bigpanda(dot)com
> >
>
> I wonder if the following might be a bit faster? Or would it be slower?
>
> select salary into temp saltemp from employee order by salary desc
> limit 5;
>
> select min(salary) as minsal into test minsal from saltemp;
>
> select name, salary from employee, minsal
> where salary >= minsal;
>
> I don't know the speed difference between my second select and your
> subselect within an EXISTS clause might be.

Not sure. Probably depends on if the subplan/seq scan for the
subquery on the one is more or less expensive than the time on the
additional query and processing for the query with the min.

Actually, i guess you might be able to use offset in there to get rid
of the second query too...
if you do like
select salary into temp saltemp from employee order by salary desc
limit 1 offset 4;
you can probably get the 5th one right out.