Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting ?pages? of rows that I?m displaying in a web > page.
OFFSET is inefficient because the database still has to compute all the rows before skipping over them. To do paging, remember the first and last date values on the page, and for the previous/next page, just continue from there: SELECT ... FROM MyTable WHERE date > :LastDateOnPreviousPage ORDER BY date LIMIT 25; (If dates are not unique, you have to use more columns.) > Now, instead of a simple select, I?d like to execute a recursive query > using CTE, because the rows are organized in an hierarchy. And I?m only > interested by a page, let's say the first n root records. What exactly do you mean with the last sentence? Are you implying that the page contains all children, regardless of how many there are? Regards, Clemens
