Re: [HACKERS] What about LIMIT in SELECT ?

Tatsuo Ishii wrote:

> I think we have understanded your point. set query_limit is just a
> easy alternative of using cursor and fetch.
>
> >    I  haven't  looked  at  Tatsuo's  patch  very well. But if it
> >    limits the amount of data going into the sort (on ORDER  BY),
> >    it  will  break it! The requested ordering could be different
> >    from what the choosen index might return. The used  index  is
> >    choosen by the planner upon the qualifications given, not the
> >    ordering wanted.
>
> I think it limits the final result. When query_limit is set,
> the arg "numberTuples" of ExecutePlan() is set to it instead of 0
> (this means no limit).
>
> Talking about "offset," it shouldn't be very difficult. I guess all we
> have to do is adding a new arg "offset" to ExecutePlan() then making
> obvious modifications. (and of course we have to modify set
> query_limit syntax but it's trivial)

    The offset could become

        FETCH n IN cursor [OFFSET n];

    and

        SELECT ... [LIMIT offset,count];

    The  FETCH command already calls ExecutorRun() with the given
    count (the tuple limit). Telling it the offset too is  really
    simple.   And  ExecutorRun()  could  check  if  the  toplevel
    executor node is an index scan. Skipping  tuples  during  the
    index  scan  requires,  that  all  qualifications  are in the
    indexqual, thus any tuple returned by it will become a  final
    result  row  (as it would be in the simple 1-table-queries we
    discussed).  If  that  isn't  the  case,  the  executor  must
    fallback to skip the final result tuples and that is after an
    eventually processed sort/merge of the complete  result  set.
    That would only reduce communication to the client and memory
    required there to buffer the result  set  (not  a  bad  thing
    either).

    ProcessQueryDesc()  in tcop/pquery.c also calls ExecutorRun()
    but with a constant 0 tuple count. Having offset and count in
    the  parsetree  would  make it without any state variables or
    SET command. And it's the only clean way to restrict LIMIT to
    SELECT  queries.  Any  thrown  in LIMIT to ExecutorRun() from
    another place could badly hurt the rewrite  system.  Remember
    that   non-instead   actions   on   insert/update/delete  are
    processed before the  original  query!  And  what  about  SQL
    functions that get processed during the evaluation of another
    query (view using an SQL function for count(*))?

    A little better would it be to make the LIMIT values able  to
    be  parameter  nodes. C or PL functions use the prepared plan
    feature  of  the  SPI  manager   for   performance   reasons.
    Especially  the  offset  value  might  there  need  to  be  a
    parameter that the executor has to pick  out  first.   If  we
    change  the  count  argument of ExecutorRun to a List *limit,
    this one could be NIL (to mean  the  old  0  count  0  offset
    behaviour)  or a list of two elements that both can be either
    a Const or a Param of type int4.  Easy for  the  executor  to
    evaluate.

    The   only   places   where   ExecutorRun()   is  called  are
    tcop/pquery.c  (queries  from  frontend),  commands/command.c
    (FETCH  command),  executor/functions.c  (SQL  functions) and
    executor/spi.c (SPI manager). So it is  easy  to  change  the
    call interface too.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #