8. Performant SQL

Activity 10: Query Planning

Solution:

1. Open PostgreSQL and connect to the sqlda database:

   C:\> psql sqlda

2. Use the EXPLAIN command to return the query plan for selecting all available records within the customers table:

   sqlda=# EXPLAIN SELECT * FROM customers;

   This query will produce the following output from the planner:

   Figure 8.75: Plan for all records within the customers table

   The setup cost is 0, the total query cost is 1536, the number of rows is 50000, and the width of each row is 140. The cost is actually in cost units, the number of rows is in rows, and the width is in bytes.

3. Repeat the query from step 2 of this activity, this time limiting the number of returned records to 15:

   sqlda=# EXPLAIN SELECT * FROM customers LIMIT 15;

   This query will produce the following output from the planner:

   Figure 8.76: Plan for all records within the customers table with the limit as 15

   Two steps are involved in the query, and the limiting step costs 0.46 units...