uF45cEHy

1DROP TABLE IF EXISTS a;
2DROP TABLE IF EXISTS b;
3
4-- create table b(id, name)
5CREATE TABLE b(
6    id SERIAL NOT NULL,
7    name VARCHAR,
8    PRIMARY KEY(id)
9);
10
11INSERT INTO b(name)
12SELECT CONCAT('b', i.*) FROM generate_series(1, 1000000) AS i;
13
14-- create table a(id, name, b_id)
15CREATE TABLE a(
16    id SERIAL NOT NULL,
17    name VARCHAR,
18    b_id INTEGER REFERENCES b(id),
19    PRIMARY KEY(id)
20);
21
22-- one row out of 3 has NULL for b_id
23INSERT INTO a(name, b_id)
24SELECT CONCAT('a', b.id), (CASE WHEN b.id % 3 = 0 THEN NULL ELSE b.id END) FROM b;
25
26ANALYZE;
27
28-- same query plans, same output
29EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
30EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
31
32-- same query plans, with 7404 and 11111 rows as output, respectively
33EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;
34EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;
35
36-- add an index between a and b
37CREATE INDEX a_b_id ON a(b_id);
38ANALYZE;
39
40-- same query plans, same output
41EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
42EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
43
44-- same query plans, with 7404 and 11111 rows as output, respectively
45EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;
46EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;