От: Mark Kirkwood
Тема: Re: Suspending SELECTs
Дата: ,
Msg-id: 43CD8B45.4090907@paradise.net.nz
(см: обсуждение, исходный текст)
Ответ на: Re: Suspending SELECTs  ()
Ответы: Re: Suspending SELECTs  (Tom Lane)
Список: pgsql-performance

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

Suspending SELECTs  (Alessandro Baretta, )
 Re: Suspending SELECTs  (Tom Lane, )
  Re: Suspending SELECTs  (Alvaro Herrera <-ip.org>, )
   Re: Suspending SELECTs  (Tom Lane, )
  Re: Suspending SELECTs  ("Craig A. James", )
   Re: Suspending SELECTs  (Alessandro Baretta, )
    Re: Suspending SELECTs  ("Jim C. Nasby", )
    Re: Suspending SELECTs  (Mark Lewis, )
    Re: Suspending SELECTs  ("Craig A. James", )
  Re: Suspending SELECTs  (Alessandro Baretta, )
   Re: Suspending SELECTs  (Michael Stone, )
   Re: Suspending SELECTs  (Tom Lane, )
   Re: Suspending SELECTs  (, )
    Re: Suspending SELECTs  (Frank Wiles, )
    Re: Suspending SELECTs  (Mark Kirkwood, )
     Re: Suspending SELECTs  (Tom Lane, )
      Re: Suspending SELECTs  (Mark Kirkwood, )
    Re: Suspending SELECTs  (Alessandro Baretta, )
     Re: Suspending SELECTs  (Tino Wildenhain, )
     Re: Suspending SELECTs  (, )
      Re: Suspending SELECTs  (Alessandro Baretta, )
       Re: Suspending SELECTs  (Harry Jackson, )
        Re: Suspending SELECTs  (, )
   Re: Suspending SELECTs  (Josh Berkus, )
    Re: Suspending SELECTs  (Josh Berkus, )
     Re: Suspending SELECTs  (Alessandro Baretta, )
      Re: Suspending SELECTs  (August Zajonc, )
       Re: Suspending SELECTs  (Alessandro Baretta, )
 Re: Suspending SELECTs  (Mark Lewis, )

 wrote:
> On Tue, Jan 17, 2006 at 08:56:00PM +0100, Alessandro Baretta wrote:
>
>
> What is wrong with LIMIT and OFFSET? I assume your results are ordered
> in some manner.
>
> Especially with web users, who become bored if the page doesn't flicker
> in a way that appeals to them, how could one have any expectation that
> the cursor would ever be useful at all?
>
> As a 'general' solution, I think optimizing the case where the same
> query is executed multiple times, with only the LIMIT and OFFSET
> parameters changing, would be a better bang for the buck. I'm thinking
> along the lines of materialized views, for queries executed more than
> a dozen times in a short length of time... :-)
>
> In the mean time, I successfully use LIMIT and OFFSET without such an
> optimization, and things have been fine for me.
>

Second that.

I do seem to recall a case where I used a different variant of this
method (possibly a database product that didn't have OFFSET, or maybe
because OFFSET was expensive for the case in point), where the ORDER BY
key for the last record on the page was saved and the query amended to
use it filter for the "next' screen - e.g:

1st time in:

SELECT ... FROM table WHERE ... ORDER BY id LIMIT 20;

Suppose this displays records for id 10000 -> 10020.
When the user hits next, and page saves id=10020 in the session state
and executes:

SELECT ... FROM table WHERE ... AND id > 10020 ORDER BY id LIMIT 20;

Clearly you have to be a little careful about whether to use '>' or '>='
depending on whether 'id' is unique or not (to continue using '>' in the
non unique case, you can just save and use all the members of the
primary key too).

Cheers

Mark


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

От: Mark Kirkwood
Дата:
Сообщение: Re: Suspending SELECTs
От: Alessandro Baretta
Дата:
Сообщение: Re: Suspending SELECTs