Nesting loops

In this recipe, we will be discussing the nesting loops mechanism in PostgreSQL.

Getting ready

Nesting loops is one of the table joining mechanisms, in which PostgreSQL prefers to join two different datasets based on a join condition. The name itself describes the nesting loops as a loop inside another loop. The outer loop holds a dataset and compares each tuple with the dataset that holds an inner loop. That is, if the outer loop has N number of tuples and the inner loop has M number of tuples, then the nested loop performs N * M number of comparisons to produce the output.

How to do it…

1. For demonstrating the nesting loops, let's run a join query at benchmarksql to retrieve the list of warehouse names along with the customer name that got the product from that warehouse:

   benchmarksql=# EXPLAIN SELECT w_name, c_first FROM bmsql_warehouse,
         bmsql_customer WHERE w_id=c_w_id;
                                                    QUERY PLAN                        ...