How to prevent execution plan changes for specific values

Question

I have a table in PosgreSQL 9.1.9. There is a diagram:

CREATE TABLE chpl_text
(
  id integer NOT NULL DEFAULT nextval('chpl_text_id_seq1'::regclass),
  page_id bigint NOT NULL,
  page_idx integer NOT NULL,
  ...
);

I have about 40,000,000 (40M) rows in this table. Now there is a request:

SELECT
  ...
FROM chpl_text
ORDER BY id
LIMIT 100000
OFFSET N

Everything works smoothly for N <= 5300000. The implementation plan is as follows

Limit  (cost=12743731.26..12984179.02 rows=100000 width=52)
  ->  Index Scan using chpl_text_pkey on chpl_text t  (cost=0.00..96857560.86 rows=40282164 width=52)

But for N >= 5400000he magically changes to

Limit  (cost=13042543.16..13042793.16 rows=100000 width=52)
  ->  Sort  (cost=13029043.16..13129748.57 rows=40282164 width=52)
        Sort Key: id
        ->  Seq Scan on chpl_text t  (cost=0.00..1056505.64 rows=40282164 width=52)

The result is very long.

How can I prevent postresql from changing the query plan for higher offsets?

Note. I know that large offsets are not very good, but I have to use them here.

Answer 1

Postgres , (ANALYZE autovacuum), , Postgres , . :
PostgreSQL

, ( !)

SET enable_seqscan=OFF;

.

EXPLAIN ANALYZE ...

, Postgres 9.2 " " . .

CTE row_number() , :

WITH cte AS (
   SELECT ..., row_number() OVER (ORDER BY id) AS rn
   FROM   chpl_text
   )
SELECT ...
FROM   cte
WHERE  rn BETWEEN N+1 AND N+100000
ORDER  BY id;

, .