Re: Paged Query

What language are you using? Usually there is iterator with chunked fetch
option (like setFetchSize in java jdbc). So you are passing query without
limit and then read as many results as you need. Note that query plan in
this case won't be optimized for your limit and I don't remember if
postgres has "optimize for N rows" statement option.
Also, if your statement is ordered by some key, you can use general paging
technique when you rerun query with "key>max_prev_value" filter to get next
chunk.

Середа, 4 липня 2012 р. користувач Hermann Matthes <hermann(dot)matthes(at)web(dot)de>
написав:
> I want to implement a "paged Query" feature, where the user can enter in
a dialog, how much rows he want to see. After displaying the first page of
rows, he can can push a button to display the next/previous page.
> On database level I could user "limit" to implement this feature. My
problem now is, that the user is not permitted to view all rows. For every
row a permission check is performed and if permission is granted, the row
is added to the list of rows sent to the client.
> If for example the user has entered a page size of 50 and I use "limit
50" to only fetch 50 records, what should I do if he is only permitted to
see 20 of these 50 records? There may be more records he can view.
> But if I don't use "limit", what happens if the query would return
5,000,000 rows? Would my result set contain 5,000,000 rows or would the
performance of the database go down?
>
> Thanks in advance
> Hermann
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

--
Best regards,
Vitalii Tymchyshyn