Heroku上PostgreSQL索引未被使用的慢查询排查求助

针对本地查询正常使用索引、但Heroku上执行全表扫描的慢查询问题，可按以下步骤排查解决：

1. 刷新数据库统计信息 #

PostgreSQL查询优化器依赖表的统计信息选择执行计划，Heroku环境的统计信息可能因数据更新未及时刷新，导致优化器误判索引成本。执行以下命令强制更新统计：

ANALYZE companies_employee;

执行后重新运行查询，观察执行计划是否切换为索引扫描。

2. 对比本地与Heroku的PostgreSQL配置参数 #

部分配置参数直接影响优化器决策，重点检查以下几项：

• effective_cache_size：该参数告知优化器系统可用缓存大小，Heroku默认值可能远低于本地环境，导致优化器认为索引扫描需要更高磁盘IO成本。执行SHOW effective_cache_size;对比本地和Heroku的值，若Heroku值过小，可通过Heroku PostgreSQL控制台调整（需对应计划支持自定义配置）。
• random_page_cost：该参数设置随机读取的成本系数，Heroku默认可能设为较高值（如4），而本地可能为2，这会让优化器更倾向于全表扫描。执行SHOW random_page_cost;对比，必要时调整为与本地接近的值。
• work_mem：影响排序操作的内存分配，若Heroku的work_mem过小，会导致排序效率低下，可作为辅助检查项。

3. 强制使用索引（临时应急方案） #

若上述步骤无效，可通过PostgreSQL的索引提示强制优化器使用指定索引。在Django中通过原生SQL实现：

from django.db import connection

target_title = "INFORMATION SPECIALIST"
with connection.cursor() as cursor:
    cursor.execute("""
        SELECT seniority 
        FROM companies_employee 
        /*+ IndexScan(companies_employee companies_e_seniori_12ac68_idx) */
        WHERE seniority != '' 
          AND upper(title) LIKE '%' || %s || '%' 
        ORDER BY seniority
        LIMIT 1;
    """, [target_title])
    result = cursor.fetchone()

注意：索引提示是临时方案，优先解决统计信息或配置问题。

4. 优化现有索引结构 #

当前查询的过滤条件是seniority != ''和upper(title) LIKE '%...%'，排序字段是seniority。现有索引companies_e_seniori_12ac68_idx是(seniority, title)，但title的模糊查询无法利用前缀索引。可创建部分索引缩小索引范围，让优化器更愿意选择：

CREATE INDEX idx_employee_seniority_nonempty_upper_title 
ON companies_employee USING btree (seniority, upper(title)) 
WHERE seniority != '';

该索引仅包含seniority非空的记录，体积更小，同时seniority字段可直接用于排序，过滤upper(title)时也能减少扫描行数。

5. 验证索引状态与可用性 #

检查Heroku上的索引是否被正常使用：

SELECT idx_scan, indexname 
FROM pg_stat_user_indexes 
WHERE relname = 'companies_employee' 
AND indexname IN ('companies_e_seniori_12ac68_idx', 'title_seniority');

若idx_scan为0，说明索引从未被使用，需进一步检查统计信息或配置。若索引存在异常，可重建索引：

REINDEX INDEX companies_e_seniori_12ac68_idx;

内容的提问来源于stack exchange，提问作者racinjasin