Nested Loop

You are looking at an older version of the documentation. The latest version is found here.

Nested loop joins are typically considered slow because of the amount of comparisons that must be done. In the diagram below, both tables have five rows, resulting in a total of 25 comparisons (5 x 5). With larger tables, this can result in a very large number of comparisons. This can result in intense CPU and memory usage.

Wagner, Bert. “Visualizing Merge Join Internals and Understanding Their Implications.” Data with Bert Atom, https://bertwagner.com/posts/visualizing-nested-loops-joins-and-understanding-their-implications/

Nested loops are used when a cross join is specified:

SELECT
    sod.salesorderid
    ,soh.salesorderid
FROM
    dwh.SalesOrderDetail sod
    CROSS JOIN pgsql_local_data.SalesOrderHeader soh
OPTION $ALLOW_CARTESIAN ALWAYS
;;

Note the use of OPTION $ALLOW_CARTESIAN ALWAYS. By default, cross joins (also called cartesian joins) are prohibited to prevent users from accidentally creating a cross join and impacting performance.