eM8xnH6i

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, b WHERE b.id = a.b_id AND b.name LIKE 'b42%' ORDER BY b.name;
30EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
31EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
32
33-- same query plans, with 7404 and 11111 rows as output, respectively
34EXPLAIN ANALYZE SELECT a.id FROM a, b WHERE b.id = a.b_id AND (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;
35EXPLAIN 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;
36EXPLAIN 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;
37
38-- same experiment with an index between a and b
39CREATE INDEX a_b_id ON a(b_id);
40ANALYZE;
41
42-- same query plans, same output
43EXPLAIN ANALYZE SELECT a.id FROM a, b WHERE b.id = a.b_id AND b.name LIKE 'b42%' ORDER BY b.name;
44EXPLAIN ANALYZE SELECT a.id FROM a JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
45EXPLAIN ANALYZE SELECT a.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE b.name LIKE 'b42%' ORDER BY b.name;
46
47-- same query plans, with 7404 and 11111 rows as output, respectively
48EXPLAIN ANALYZE SELECT a.id FROM a, b WHERE b.id = a.b_id AND (a.name LIKE 'a42%' OR b.name LIKE 'b42%') ORDER BY b.name;
49EXPLAIN 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;
50EXPLAIN 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;