6. Performant SQL

Activity 6.01: Query Planning

Note that the performance metrics produced by the output of query execution plan will vary based on system configuration.

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 6.63: 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 6.64: Plan for all records...