Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Fetching pre-joined rows from one table with a simple select
-- Simulates an embedded document model appropriate for access patterns
--   that require all the data, all the time. Store it in one place.

create table wordsn( primary key(id, n), id bigint, n int, word text);
insert into  wordsn select generate_series(1,100000), 1, 'Hello World';

explain (analyze, buffers, costs off)
select word from wordsn;
drop table wordsn;

CREATE TABLE

INSERT 0 100000

QUERY PLAN

Seq Scan on wordsn (actual time=0.012..13.464 rows=100000 loops=1)

  Buffers: shared hit=637

Planning:

  Buffers: shared hit=2

Planning Time: 0.076 ms

Execution Time: 18.486 ms

EXPLAIN

DROP TABLE

-- Fetching multiple rows from one table with a simple select
-- Simulates a single collection pattern appropriate when items are frequently accessed
--  independently of each other. Store data across related items on the same table.

create table wordsn( primary key(id, n), id bigint, n int, word text);

insert into  wordsn 
  select generate_series(1,100000), 1, 'Hello'
  union all
  select generate_series(1,100000), 2, 'World'
  order by 1,2,3;

explain (analyze, buffers, costs off)
select word from wordsn

CREATE TABLE

INSERT 0 200000

QUERY PLAN

Seq Scan on wordsn (actual time=0.012..23.137 rows=200000 loops=1)

  Buffers: shared hit=1274

Planning:

  Buffers: shared hit=2

Planning Time: 0.070 ms

Execution Time: 32.917 ms

EXPLAIN

-- Joining from two tables - most expensive way to access the data.

create table words1( primary key(id), id bigint, word text);
insert into  words1 select generate_series(1,100000), 'Hello';
create table words2( primary key(id), id bigint, word text);
insert into  words2 select generate_series(1,100000), 'World';

explain (analyze, buffers, costs off)
select words1.word, words2.word from words1 join words2 using(id) 
order by id

CREATE TABLE

INSERT 0 100000

CREATE TABLE

INSERT 0 100000

QUERY PLAN

Merge Join (actual time=0.179..64.222 rows=100000 loops=1)

  Merge Cond: (words1.id = words2.id)

  Buffers: shared hit=1632

  ->  Index Scan using words1_pkey on words1 (actual time=0.160..17.192 rows=100000 loops=1)

        Buffers: shared hit=816

  ->  Index Scan using words2_pkey on words2 (actual time=0.014..15.658 rows=100000 loops=1)

        Buffers: shared hit=816

Planning:

  Buffers: shared hit=22

Planning Time: 3.170 ms

Execution Time: 70.447 ms

EXPLAIN