Problema Paginação Postgre com Offset

[TeraHertz]

Pessoal sou novato no postgre, meu problema é o seguinte:

Tenho uma tabela com 45+ milhões de registros, usando as cláusulas LIMIT e OFFSET posso paginar até uns 150.000 registros sem uma perda de performance.

Porém acima disso (falta muiiitoo para chegar nos 45+ milhões) é impraticável....

Como soluciono isso ? procedures ? cursores ? functions ?

Gostaria da opinião de vocês já pesquisei muito porém todos os exemplos de paginação que encontrei utilizam LIMIT e OFFSET... o pessoal que souber outras formas deêm uma mão e também deixem um exemplo de como fazer, pois sou novato.

OBS: a tabela já possui indice, pelo que pesquisei utilizando OFFSET a consulta fica lenta por causa que simplesmente ela percorre todos os registros 0...m e só mostra apartir do OFFSET passado, precisaria algo como percorrer somente apartir do offset passado ai sim não teria problemas.

AGUARDO AJUDA !!!

[Gusmon]

Tive um problema semelhante, no meu caso resolvi criando uma function e setando indice na mesma, não sei bem como faz as consultas, espero ter ajudado.

[Kakao]

Mostre a consulta que você está usando.

[TeraHertz]

Pessoal obrigado pelas respostas.

Bom o sql seria + ou - assim:

SELECT uuid, name, msg, date ..... FROM msg_users OFFSET 7500100 LIMIT 20

o problema é que acima do OFFSET 150000 já vai ficando cada vez mais lento....

[Kakao]

Não tem as cláusulas WHERE ou ORDER BY?

Editado Junho 24, 2012 por Kakao

[TeraHertz]

WHERE não tem, ORDER BY tinha colocado date

[Kakao]

O mais simples é tentar restringir ao máximo o conjunto com uma condição no WHERE. A alternativa não é trivial. Uma solução é criar uma coluna de inteiros com a ordenação desejada e quando for consultar usar esta coluna na cláusula WHERE:

create table t (id int, name text, ordem_name int unique);

insert into t (id, name) values
(1, 'fulano'),(2, 'sicrano'),(3, 'beltrano')
;

update t
set ordem_name = s.ordem
from (
    select id, row_number() over(order by name) as ordem
    from t
) s
where t.id = s.id
;

select * from t;
 id |   name   | ordem_name 
----+----------+------------
  1 | fulano   |          2
  2 | sicrano  |          3
  3 | beltrano |          1
(3 rows)

Agora use a cláusula where para fazer o OFFSET e o LIMIT

select *
from t
where ordem_name >= 2 and ordem_name < 2 + 2
;
 id |  name   | ordem_name 
----+---------+------------
  1 | fulano  |          2
  2 | sicrano |          3
(2 rows)

O update deverá ser feito regularmente, por exemplo a cada 3 horas. Se for necessário manter sempre atualizado então a coluna ordem_name deve ser uma função.

Editado Junho 25, 2012 por Kakao