ollionparas.sql

-- Tested on
-- PostgreSQL 9.6.2 (rds, db.t2.micro)
-- PostgreSQL 10.3 (rds, db.t2.micro)
create table stuff (
  id bigserial primary key,
  firstname text,
  lastname text,
  amount integer,
  created_at timestamp default current_timestamp
);

create table meheh (
  id bigserial primary key,
  category text,
  stuff_id bigint references stuff(id),
  created_at timestamp default current_timestamp
);



insert into stuff (
  firstname, lastname, amount
)
select
  i,
  i,
  i
from generate_series(1, 100000) s(i);


-- run this 5-10 times
insert into meheh (
  category, stuff_id
)
select
  i,
  i
from generate_series(1, 100000) s(i);

results.sql

root=> explain analyze select * from stuff inner join meheh on meheh.stuff_id = stuff.id limit 100 offset 299900;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=17429.20..17433.79 rows=100 width=59) (actual time=587.948..588.149 rows=100 loops=1)
   ->  Hash Join  (cost=3670.00..31197.58 rows=600000 width=59) (actual time=56.336..532.928 rows=300000 loops=1)
         Hash Cond: (meheh.stuff_id = stuff.id)
         ->  Seq Scan on meheh  (cost=0.00..10412.00 rows=600000 width=29) (actual time=0.009..149.472 rows=599952 loops=1)
         ->  Hash  (cost=1736.00..1736.00 rows=100000 width=30) (actual time=56.171..56.171 rows=100000 loops=1)
               Buckets: 65536  Batches: 2  Memory Usage: 3644kB
               ->  Seq Scan on stuff  (cost=0.00..1736.00 rows=100000 width=30) (actual time=0.005..24.508 rows=100000 loops=1)
 Planning time: 0.222 ms
 Execution time: 590.610 ms
(9 rows)


root=> explain analyze select * from stuff inner join meheh on meheh.stuff_id = stuff.id where stuff.id in (select stuff.id from stuff limit 100 offset 299900);
                                                                    QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1736.34..14400.07 rows=6 width=59) (actual time=44.063..44.063 rows=0 loops=1)
   ->  Hash Join  (cost=1736.05..14398.11 rows=6 width=37) (actual time=44.061..44.061 rows=0 loops=1)
         Hash Cond: (meheh.stuff_id = stuff_1.id)
         ->  Seq Scan on meheh  (cost=0.00..10412.00 rows=600000 width=29) (actual time=0.008..0.008 rows=1 loops=1)
         ->  Hash  (cost=1736.04..1736.04 rows=1 width=8) (actual time=44.047..44.047 rows=0 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 8kB
               ->  HashAggregate  (cost=1736.03..1736.04 rows=1 width=8) (actual time=44.047..44.047 rows=0 loops=1)
                     Group Key: stuff_1.id
                     ->  Limit  (cost=1736.00..1736.02 rows=1 width=8) (actual time=44.043..44.043 rows=0 loops=1)
                           ->  Seq Scan on stuff stuff_1  (cost=0.00..1736.00 rows=100000 width=8) (actual time=0.005..26.011 rows=100000 loops=1)
   ->  Index Scan using stuff_pkey on stuff  (cost=0.29..0.32 rows=1 width=30) (never executed)
         Index Cond: (id = meheh.stuff_id)
 Planning time: 0.370 ms
 Execution time: 44.121 ms
(14 rows)