面试题：PostgreSQL中全表扫描与索引扫描在何种场景下性能差异显著

全表扫描与索引扫描性能差异场景

数据量
- 全表扫描更优：当表数据量非常小，例如只有几十条记录时，由于数据量小，全表扫描的开销很低，相比之下，索引的维护和查找也需要一定开销，此时全表扫描性能可能更好。例如，一个简单的配置表，存储系统中固定的几个参数，记录数很少。
- 索引扫描更优：随着数据量的大幅增加，例如表中有百万级甚至更多记录时，全表扫描需要遍历每一条记录，开销巨大。而索引扫描可以通过索引快速定位到符合条件的数据，性能优势明显。如一个电商平台的订单表，记录了大量用户的订单信息。
查询条件
- 全表扫描更优：
  - 无过滤条件：当查询不包含任何过滤条件，如 SELECT * FROM table_name;，此时全表扫描是必然选择，因为没有条件可以利用索引来优化。
  - 索引列使用函数或表达式：如果在索引列上使用了函数或表达式，例如 SELECT * FROM users WHERE UPPER(username) = 'ADMIN';，PostgreSQL 通常无法使用索引，只能进行全表扫描。因为索引是基于列的原始值建立的，函数或表达式改变了列的值，无法直接利用索引。
- 索引扫描更优：
  - 等值查询：对于等值查询，如 SELECT * FROM products WHERE product_id = 123;，如果 product_id 列上有索引，PostgreSQL 可以快速通过索引定位到对应的记录，索引扫描性能好。
  - 范围查询：在范围查询中，例如 SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;，如果 salary 列有索引，索引可以帮助快速定位到符合范围的记录，减少扫描的数据量，相比全表扫描性能提升显著。
表结构
- 全表扫描更优：如果表的列数非常多，索引扫描虽然可以快速定位到记录的位置，但从磁盘读取完整记录时，由于列数多，I/O 开销较大。此时，对于某些查询，如果全表扫描可以在一次顺序 I/O 操作中读取到所需数据，性能可能优于索引扫描。例如一个包含大量历史数据的日志表，记录了各种详细信息，列数众多。
- 索引扫描更优：当表中有选择性高的列（即不同值的比例高），并且这些列经常用于查询条件时，在这些列上创建索引，索引扫描性能会很好。例如，在一个用户表中，email 列具有较高的选择性，因为每个用户的邮箱通常是唯一的，对 email 列创建索引后，在查询特定用户时，索引扫描能迅速定位到目标记录。

示例

假设有一个 employees 表，表结构如下：

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary NUMERIC(10, 2),
    department VARCHAR(50)
);

全表扫描示例
- 无过滤条件：

SELECT * FROM employees;

- **索引列使用函数**：

SELECT * FROM employees WHERE UPPER(first_name) = 'JOHN';

索引扫描示例
- 等值查询：

SELECT * FROM employees WHERE employee_id = 10;

- **范围查询**：

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;

星途面试题库

面试题：PostgreSQL中全表扫描与索引扫描在何种场景下性能差异显著

知识考点

面试题答案

全表扫描与索引扫描性能差异场景

示例