Skip to content

Instantly share code, notes, and snippets.

@jrasanen

jrasanen/ollionparas.sql

Last active Apr 4, 2018
Embed
What would you like to do?
-- 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);
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment