\echo
\echo # Create two tables with a non-nullable foreign key:
\echo

CREATE TABLE album
  ( id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
  );
CREATE INDEX album_title ON album (title);

CREATE TABLE track
  ( id SERIAL PRIMARY KEY,
    album_id INT NOT NULL REFERENCES album(id),
    title TEXT NOT NULL
  );
CREATE INDEX track_album ON track(album_id);
CREATE INDEX track_title ON track(title);

\echo
\echo ### Fill them with some data:
\echo

CREATE TABLE words
  ( id SERIAL PRIMARY KEY,
    word TEXT NOT NULL
  );

COPY words (word, id) FROM stdin;
a	1
hey	2
woop	3
i	4
am	5
a	6
coffee	7
pot	8
mouse	9
solaria	10
moon	11
terrasse	12
toilet	13
paper	14
pencil	15
keyboard	16
window	17
tile	18
\.

CREATE FUNCTION title_sample() RETURNS TEXT AS $$
  SELECT string_agg(word, ' ')
  FROM (
    SELECT word
    FROM words TABLESAMPLE BERNOULLI (50)
    ) w
  $$ LANGUAGE SQL VOLATILE;

INSERT INTO album (title)
  (
    SELECT title
    FROM (
      SELECT generate_series(1,1000), title_sample() AS title
    ) s
  );

INSERT INTO track (title, album_id)
  SELECT t.title, album.id AS album_id
  FROM (
    SELECT title
    FROM (
      SELECT generate_series(1,10),  title_sample() AS title
    ) t
  ) t
  CROSS JOIN album;

\echo
\echo # Observe the differences in the plans produced:

\echo
\echo ## Explain inner join:

EXPLAIN ANALYZE
SELECT
  t.id
FROM
  track t
  INNER JOIN album a ON (t.album_id = a.id)
ORDER BY
  a.title ASC
LIMIT 10;

\echo
\echo ## Explain outer join:

EXPLAIN ANALYZE
SELECT
  t.id
FROM
  track t
  LEFT JOIN album a ON (t.album_id = a.id)
ORDER BY
  a.title ASC
LIMIT 10;

