offset.sql

CREATE TABLE ticket AS
SELECT
    id,
    TRUNC(RANDOM() * 100 + 1) AS project_id,
    NOW() + (RANDOM() * (NOW()+'365 days' - NOW())) AS created_date,
    repeat((TRUNC(RANDOM() * 100 + 1)::text), 1000) as payload
FROM GENERATE_SERIES(1, 1000000) AS g(id);

CREATE INDEX simple_index ON ticket using btree(project_id, created_date);

VACUUM FULL;
VACUUM ANALYZE ticket;

SET work_mem = '512MB';
SET random_page_cost = 1.0;

EXPLAIN ANALYZE
with r as (
 select TRUNC(RANDOM() * 100 + 1) as id
) 
SELECT * FROM ticket
WHERE project_id = (select id from r)
AND created_date > '20.06.2017'
ORDER BY created_date offset XXXXX limit 100;

-- offset 100 1.3ms vs 0.7ms
-- offset 1000 5.6ms vs 1.1ms
-- offset 10000 46.7ms vs 3.6ms

Also, works for such queries:

EXPLAIN ANALYZE
WITH _id AS (
    SELECT 
    TRUNC(RANDOM() * 100 + 1) 
    AS id
)
select * from 
(
	select * from ticket where project_id = (SELECT id FROM _id)
) as x
limit 100 offset 9000

But not for such:

EXPLAIN ANALYZE
WITH _id AS (
    SELECT 
    TRUNC(RANDOM() * 100 + 1) 
    AS id
)
select * from 
(
	select * from ticket where project_id = (SELECT id FROM _id)
) as x
where project_id / 2 != 100
limit 100 offset 9000