От: Albe Laurenz
Тема: Re: Paged Query
Дата: ,
Msg-id: D960CB61B694CF459DCFB4B0128514C2081BF026@exadv11.host.magwien.gv.at
(см: обсуждение, исходный текст)
Ответ на: Paged Query  (Hermann Matthes)
Список: pgsql-performance

Скрыть дерево обсуждения

Paged Query  (Hermann Matthes, )
 Re: Paged Query  (Віталій Тимчишин, )
 Re: Paged Query  ("Albe Laurenz", )
 Re: Paged Query  (Greg Spiegelberg, )
 Re: Paged Query  (Misa Simic, )
  Re: Paged Query  (Gregg Jaskiewicz, )
   Re: Paged Query  (Craig Ringer, )
    Re: Paged Query  (Shaun Thomas, )
     Re: Paged Query  (Craig James, )
      Re: Paged Query  (Greg Spiegelberg, )
     Re: Paged Query  (Craig Ringer, )
   Re: Paged Query  (Misa Simic, )
    Re: Paged Query  (Andrew Dunstan, )
     Re: Paged Query  (Gurjeet Singh, )
    Re: Paged Query  (Віталій Тимчишин, )
    Re: Paged Query  (Віталій Тимчишин, )
 Re: Paged Query  (Misa Simic, )
  Re: Paged Query  (Craig Ringer, )
   Re: Paged Query  (Jeff Janes, )
 Re: Paged Query  (Misa Simic, )

Hermann Matthes wrote:
> I want to implement a "paged Query" feature, where the user can enter
> a dialog, how much rows he want to see. After displaying the first
> 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
> 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
> performance of the database go down?

Selecting all 5000000 rows would consume a lot of memory wherever
they are cached. Also, it might lead to bad response times (with
an appropriate LIMIT clause, the server can choose a plan that
returns the first few rows quickly).

I assume that there is some kind of ORDER BY involved, so that
the order of rows displayed is not random.

I have two ideas:
- Try to integrate the permission check in the query.
  It might be more efficient, and you could just use LIMIT
  and OFFSET like you intended.
- Select some more rows than you want to display on one page,
  perform the permission checks. Stop when you reach the end
  or have enough rows. Remember the sort key of the last row
  When the next page is to be displayed, use the remembered
  sort key value to SELECT the next rows.

Laurenz Albe

В списке pgsql-performance по дате сообщения:

От: Chris Hanks
Сообщение: Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.
От: Craig Ringer
Сообщение: Re: Create tables performance