Why can the Postgres query optimizer use Seq Scan in production but Index Scan in dev/ft with the same indexes?

Postgres chooses the query execution plan based on statistics and cost estimated by the optimizer. Even with the same indexes, different plans (Seq Scan or Index Scan) may be used in production and dev/test environments due to differences in:

• Data volume: production usually has more data, and Seq Scan might be more efficient if most of the table is read.
• Up-to-date statistics: if statistics are outdated or inaccurate, the optimizer may misjudge the cost.
• Configuration parameters: settings affecting operation costs (random_page_cost, seq_page_cost).
• Load and cache state: dev/test environments might have more free memory and cached pages, influencing the plan choice.

Seq Scan (sequential scan) is effective when reading a large portion of the table, while index scan is suitable for small selections where the index helps quickly locate the needed rows.

To address differences, you should:

• Update statistics (ANALYZE).
• Check configuration parameters.
• Compare data volume and distribution.
• Use EXPLAIN ANALYZE to analyze plans.