面试题：PostgreSQL查询优化器如何利用表统计信息进行查询优化

Question

在PostgreSQL中，表统计信息对于查询优化器至关重要。请详细阐述查询优化器是怎样利用表的行数、列的基数等统计信息来生成更高效的查询计划的。

Answer 1

利用表行数信息生成查询计划

1. 估算扫描成本
   • 查询优化器在考虑不同的扫描方式（如顺序扫描、索引扫描等）时，表的行数是重要的估算依据。例如，对于一个行数较少的表，顺序扫描可能成本较低。假设表employees只有100行，顺序扫描该表读取所有行的I/O成本相对较低，优化器可能会倾向于选择顺序扫描。而如果行数达到数百万行，顺序扫描的I/O成本会显著增加，优化器可能更倾向于使用索引扫描。
2. 连接操作中的成本估算
   • 在涉及表连接时，表的行数用于估算连接结果集的大小。例如，在进行JOIN操作时，如果优化器知道表A有1000行，表B有500行，并且连接条件是一个简单的等值连接，优化器会根据这些行数信息来估算连接后结果集的大小（假设连接条件没有特殊的选择性，结果集大小可能接近1000 * 500 ，当然实际会考虑连接条件的选择性）。根据这个估算，优化器可以选择更合适的连接算法，如嵌套循环连接、哈希连接或合并连接。对于小表连接，嵌套循环连接可能更合适；对于大表连接，哈希连接可能更高效。

利用列基数信息生成查询计划

1. 索引选择
   • 列的基数是指该列中不同值的数量。高基数列（即不同值数量较多）适合创建索引，因为索引可以快速定位到特定值的行。例如，在customers表的email列，假设该列基数较高，每个客户的邮箱基本不同。当查询SELECT * FROM customers WHERE email = '[e-mail]'时，优化器会根据email列的高基数信息，选择使用email列上的索引，因为通过索引可以快速定位到满足条件的行，而不是进行全表扫描。
2. 过滤条件评估
   • 列基数信息用于评估过滤条件的选择性。选择性高的过滤条件（即满足条件的行数占总行数的比例低）可以显著减少扫描的数据量。例如，在products表的category列，如果基数较低，只有几种产品类别，而查询SELECT * FROM products WHERE category = 'electronics'，优化器根据category列的基数信息知道这个过滤条件的选择性相对较低（因为可能有较多行满足该条件），可能不会选择使用category列上的索引（如果存在索引），而是采用其他更合适的扫描策略。相反，如果是高基数列的过滤条件，优化器更有可能使用索引来减少扫描行数。
3. 连接条件评估
   • 在表连接时，连接条件涉及的列基数信息也很关键。例如，在orders表和customers表通过customer_id列进行连接，customer_id列在两个表中的基数信息会影响连接算法的选择。如果customer_id列基数较高，说明每个customer_id值在表中出现的次数相对较少，哈希连接可能更适合，因为可以高效地构建哈希表来匹配连接条件；如果基数较低，其他连接算法可能更优。