CREATE TABLE Parent(parent_id INTEGER, extra INTEGER NOT NULL,
CONSTRAINT pk_parent PRIMARY KEY (parent_id));
CREATE TABLE Child (child_id SERIAL,
parent_id INTEGER NOT NULL,
CONSTRAINT pk_child PRIMARY KEY (child_id),
CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES Parent (parent_id));
CREATE INDEX idx_child_parent ON Child (parent_id);
INSERT INTO Parent (parent_id, extra)
SELECT v, (RANDOM()*(10-(-5)))::INT + (-5)
FROM generate_series(1, 1000) AS v;
INSERT INTO Child (parent_id)
SELECT (RANDOM()*(1000-(1)))::INT + (1)
FROM (SELECT generate_series( 1,100000)) AS foo;
ANALYZE;
The following two queries are in this case logically equvalent, i.e., produce always the same result.
However, in case of LEFT JOIN the DBMS applies table elimination technique (the query is executed based on only table Child).
In case of INNER JOIN the table elimination does not take place and the system reads both tables Parent and Child for the query execution.
EXPLAIN ANALYZE SELECT parent_id, child_id
FROM Child LEFT JOIN Parent USING (parent_id);
EXPLAIN ANALYZE SELECT parent_id, child_id
FROM Child INNER JOIN Parent USING (parent_id);