Computing basic cost

In this recipe, we will be discussing how the optimizer chooses the optimal plan from the set of plans that it generates.

Getting ready

PostgreSQL generates a set of plans based on the optimizer attributes such as enable_seqscan, enable_indexscan, and so on. Among these generated sets of plans, PostgreSQL will only choose the plan that has the minimum cost value when compared with the other plans.

How to do it…

1. Let's generate a simple plan, as shown in the following snippet, and evaluate it:

   benchmarksql=# SHOW seq_page_cost;
    seq_page_cost
   ---------------
    1
   (1 row) benchmarksql=# EXPLAIN SELECT * FROM bmsql_customer;
                                  QUERY PLAN                                
   -------------------------------------------------------------------------
    Seq Scan on bmsql_customer  (cost=0.00..28218.00 rows=300000
    width=559)
   (1 row)
   

2. The numbers that we are seeing in the preceding plan are all arbitrary cost values. That is, assumed cost values to...