Re: [HACKERS] SELECT ... LIMIT (trial implementation) - Mailing list pgsql-hackers

> Here we go,
>
>     this is up to now only for discussion, do not apply to CVS!
>
>     Those involved into the LIMIT discussion please comment.
>
>     Here is what I had in mind for the SELECT ... LIMIT.  It adds
>
>         SELECT ... [LIMIT count [, offset]]
>
>     to the parser and arranges that these values are passed  down
>     to the executor.

My only suggestion is that I don't like syntax where you have value
'a,b', and a and b have different meanings.

I would prefer:

         SELECT ... [LIMIT count [OFFSET offset]]

This makes things much clearer for people reading the query.

What if someone wants the rows from 500 to the end.  Should we allow
the syntax to be:

         SELECT ... [LIMIT count] [OFFSET offset]

LIMIT and OFFSET are independent.

>     It is a clean implementation of LIMIT (regression tested) and
>     the open items on it are to enable parameters and  handle  it
>     in  SQL  functions and SPI stuff (currently ignored in both).
>     Optimizing the executor would require  the  other  sort  node
>     stuff  discussion  first to come to a conclusion.  For now it
>     skips final result rows - but that's already one step forward
>     since  it  reduces  the  rows sent to the frontend to exactly
>     that what LIMIT requested.
>
>     I've seen the queryLimit  by  SET  variable  stuff  and  that
>     really  can  break rewrite rules, triggers or functions. This
>     is because the query limit will be  inherited  by  any  query
>     (inserts, updates, deletes too) done by them. Have a rule for
>     constraint deletes of referencing tuples
>
>         CREATE RULE del_table1 AS ON DELETE TO table1 DO
>             DELETE FROM table2 WHERE ref = OLD.key;
>
>     If the user now sets the query limit to 1 via SET and deletes
>     a row from table1, only the first found record in table2 will
>     be constraint deleted, not all of them.
>
>     This is a feature where  users  can  get  around  rules  that
>     ensure data integrity.


OK, I am all for removal of SET QUERY_LIMIT, especially if we think we
can get something better in a post-6.4 release.

I assume the current strategy for impelemting LIMIT..OFFSET is:

    For single-table queries, if the index matches the ORDER BY, use
the index to do the LIMIT..OFFSET.  Large offset value require a
sequential scan of the index until it reaches the OFFSET.

    For joins, if an index matches the ORDER BY, and the indexed
table is on the outside of a join loop, use the index to force the query
to execute in ORDER BY order, and reduce the number of values in the
query.

It would be nifty if we could peek into the index and change LIMIT to an
actual range of value that would automatically match an index, then you
have to force the optimizer to use the index,  i.e.


    SELECT * FROM tab LIMIT 100

becomes:

    SELECT * FROM tab WHERE x < 732

but that is very strange to do, and I would prefer not to approach it
that way.  Seems like Jan has already done it better than that.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026