Nested Loop

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.