Обсуждение: RE: [HACKERS] What about LIMIT in SELECT ?

> -----Original Message-----
> From: Jan Wieck [mailto:jwieck@debis.com]
> Sent: Friday, October 16, 1998 5:21 PM
> To: Hiroshi Inoue
> Cc: maillist@candle.pha.pa.us; jwieck@debis.com
> Subject: Re: [HACKERS] What about LIMIT in SELECT ?
> 
> 
> Hiroshi Inoue wrote:
> 
> > In the following cases I didn't modify my code to use index scan,
> > because I couldn't formulate how to tell PostgreSQL optimizer whether
> > the response to get first rows is needed or the throughput to process
> > sufficiently many target rows is needed.
> >
> > 3.The access plan made by current PostgreSQL optimizer for a query with
> >    ORDER BY clause doesn't include index scan.
> >
> > I thought the use of Tatsuo's QUERY_LIMIT to decide that the responce
> > is needed. It is sufficient but not necessary ?
> > In Oracle the hints FIRST_ROWS,ALL_ROWS are used.
> 
>     I  still  think  that  the  QUERY LIMIT should be part of the
>     parse tree and not thrown in  by  a  magic  SET  command.  If
>     rewriting  or  function  calls turn the one query sent to the
>     backend into multiple queries processed internal, how  should
>     this QUERY LIMIT variable know to which of all the queries it
>     has to be applied?  It can really break functions and rewrite
>     rules  if  this  variable  is used on all queries while it is
>     set.
> 
>     For your case 3 I think, if there is a  QUERY  LIMIT  in  the
>     parse  tree, the (future) optimizer definitely knows that not
>     all rows will get processed even if there is no qualification
>     given.   So  if  there is an index, that matches the ORDER BY
>     clause and it is no a join and the (future) executor  handles
>     OFFSET  in  single table index scans fast, it could choose an
>     index scan for this query too.
>

When using cursors,in most cases the response to get first(next) rows 
is necessary for me,not the throughput.
How can we tell PostgreSQL optimzer that the response is necessary ?

Hiroshi Inoue
Inoue@tpf.co.jp  

Hiroshi Inoue wrote:

> When using cursors,in most cases the response to get first(next) rows
> is necessary for me,not the throughput.
> How can we tell PostgreSQL optimzer that the response is necessary ?

    With my LIMIT patch, the offset and the row count are part of
    the querytree. And if a LIMIT is given, the limitCount elemet
    of the querytree (a Node *) isn't NULL what it is by default.

    When a LIMIT is given, the optimizer could assume that  first
    rows  is  wanted (even if the limit is ALL maybe - but I have
    to think about this some more). And this assumption might let
    it  decide  to use an index to resolve an ORDER BY even if no
    qualification was given.

    Telling the optimizer that first  rows  wanted  in  a  cursor
    operation would read

        DECLARE CURSOR c FOR SELECT * FROM mytab ORDER BY a LIMIT ALL;


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) #