Post date: Nov 16, 2014 4:42:56 PM

LIMIT and OFFSET allow you to retrieve just a portion of the rows that are generated by the rest of the query: Greenplum WINDOW function ROW_NUMBER can also be used to limit the rows.

SELECT select_list FROM table_expression [LIMIT { number | ALL }] [OFFSET number]

The query optimizer takes LIMIT into account when generating a query plan, so you are very likely to get different plans (yielding different row orders) depending on what you give for LIMIT and OFFSET. Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET can be inefficient.

I have found that window functions are actually faster than OFFSET.SELECT * FROM sachi ORDER BY name LIMIT 10 OFFSET 500

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY name ASC) AS rownum FROM sachi) AS foo WHERE rownum > 500 AND rownum <= 510