Julian Legeny <legeny@livetrade.cz> writes:
> PROBLEM IS, that when I start to retrieve records, the performance
> is poor. But when I execute manually (from a DB client) query VACUUM
> ANALYZE one more time (during retrieving of pages), the performance is
> much better.
I don't think this has anything to do with executing an additional
vacuum analyze. I think you're seeing the planner switch from an
indexscan plan to a sort-based plan. EXPLAIN ANALYZE output for
the query with different offset settings would tell you more.
In general, though, that whole approach sucks and you should get rid of
it. The backend still has to compute all the rows you are skipping with
OFFSET; there is not some magic in there to let it jump to the right
place. You'd be far better off to use a cursor and incrementally FETCH
from the cursor.
regards, tom lane