Factors Influencing Index Scan Costs

1. IO Cost: Costs related to reading pages from disk into memory. Parameters like random_page_cost and seq_page_cost are relevant here.

2. CPU Cost: Overhead of computation, usually represented by cpu_tuple_cost and cpu_index_tuple_cost.

3. Predicate Evaluation: Costs of evaluating WHERE clause conditions.

4. Sort Cost: If the query involves sorting not covered by the index.

Cost Calculation Mechanism

PostgreSQL's cost for index scans is often computed as:

Cost = (IO Cost) + (CPU Cost) + (Predicate Evaluation Cost) + (Sort Cost)

Tools for Assessment

• Use EXPLAIN ANALYZE to get a breakdown of query costs.

• Fine-tune configuration parameters for hardware-specific optimisation.

Flowchart

                  +-----------------------+
                  | Start                 |
                  +-----------------------+
                                |
                                V
             +----------------------------------+
             | Does Query Require Index Scan?   |
             +----------------------------------+
                                |
                  /------------Yes-----------\
                 |                           |
                 V                           V
 +-------------------------+       +-----------------------+
 | Use EXPLAIN ANALYZE      |       | Use Sequential Scan   |
 | to get Initial Cost      |       +-----------------------+
 +-------------------------+
                 |
                 V
  +------------------------------------------+
  | Adjust Config Parameters for Fine-Tuning |
  +------------------------------------------+
                 |
                 V
        +---------------------+
        | Re-run EXPLAIN      |
        | ANALYZE to Validate |
        +---------------------+
                 |
                 V
  +----------------------------------------+
  | Satisfied with Performance and Cost?   |
  +----------------------------------------+
                 |
       /---------No---------\
      |                      |
      V                      V
+--------------+   +-------------------------+
| Fine-Tune    |   | Query is Optimized      |
| Parameters   |   | and Ready for Execution |
+--------------+   +-------------------------+
      |
      V
   Go back to 
   "Adjust Config"

https://postgrespro.com/blog/pgsql/5969493 Detailed explanation about the topic

For further information, consider looking into these search terms:

• "PostgreSQL index scan cost formula"
• "How does PostgreSQL calculate index scan cost"