Database Reference
In-Depth Information
Joins
By now, you have already found that for a SQL query, there can be numerous and
equipollent query plans and the complexity of the query planner here is not merely
culling the index scan types and estimating the cost but it is withal about joining the
tables as well. Selecting the possible and optimal plan among the possible ways gets
involved for each incipient table integrated into the list for each join possibility. This
essentiality is going to give birth to a range of techniques available to join each table
pair. We will optically discern them piecemeal and discuss how they work and differ
from each other. A join takes the outer and inner tables or you can utilize left and
right terms interchangeably.
Let's try expressing the relation for a join between
A
and
B
. If
A
is the outer join
operand, then
B
is the inner join operand, or alternatively
A
is left and
B
is right.
Nested loop joins
A nested loop join is basically a nested
FOR
loop in which the relation on the right is
scanned once for every row found in the relation on the left. Though this is easy to
implement, it can be time consuming and useful when the inding qualifying rows
operation is cheap.
The only way to execute a
cross join
is nested loop.
The pseudocode representation of a nested loop join between
A
and
B
is as follows:
for each outer row in A:
for each inner row in B:
if join condition is true:
output resultant row
In the latest PostgreSQL versions, improvement has been made to the planner's ability
to use nested loops with inner index scans. Let's look at the query plan for this type of
join using the
warehouse_tbl
and
history
tables:
warehouse_db=# EXPLAIN SELECT * FROM record.warehouse_tbl,
record.history;
QUERY PLAN
------------------------------------------------------------------
Nested Loop (cost=0.00..6934581.05 rows=400000000 width=291)
-> Seq Scan on history (cost=0.00..1934580.00 rows=100000000
width=46)
-> Materialize (cost=0.00..1.06 rows=4 width=245)
