Обсуждение: Is there a way too speed up Limit with high OFFSET ?

Hi,
I have query like this
Select * from tabelname limit 10 OFFSET 10;

If i increase the OFFSET to 1000 for example, the query runs slower . The
bigger is OFFSET the slower is the query.

This is standard pagination feature i use for my website.
Actually the query is little bit more complex than this, but it is generally a
select with a join.

So i wander if there is a way to speed up this kind of query ?

I'm now reading about windowing functions , but I'm not sure this is the way
to go for this feature.

Le 12/08/2010 09:43, Georgi Ivanov a écrit :
> [...]
> I have query like this
> Select * from tabelname limit 10 OFFSET 10;
>
> If i increase the OFFSET to 1000 for example, the query runs slower . The
> bigger is OFFSET the slower is the query.
>
> This is standard pagination feature i use for my website.
> Actually the query is little bit more complex than this, but it is generally
> a
> select with a join.
>
> So i wander if there is a way to speed up this kind of query ?
>

You should better use cursors, than the OFFSET/LIMIT clause.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Hello

2010/8/12 Georgi Ivanov <georgi.r.ivanov@gmail.com>:
> Hi,
> I have query like this
> Select * from tabelname limit 10 OFFSET 10;
>
> If i increase the OFFSET to 1000 for example, the query runs slower . The
> bigger is OFFSET the slower is the query.
>

sure - database have to process all rows and skip a first n of result.
This method isn't very efective.

> This is standard pagination feature i use for my website.
> Actually the query is little bit more complex than this, but it is generally
> a
> select with a join.
>
> So i wander if there is a way to speed up this kind of query ?
>
> I'm now reading about windowing functions , but I'm not sure this is the way
> to go for this feature.
>

yes, you have to store a last primary key on page. Then if you wont to
show a next page, you have to select like

SELECT * FROM tablename WHERE id > last_primary_key_on_previous_page LIMIT 10.

When this method isn't possible, then you have to ensure the fast
processing of query without OFFSET. Maybe you have to add index,
increate a work_mem, ...

Regards

Pavel Stehule

>
>