优化器未使用索引案例分析

现有物化视图 mv_union_auto_order已创建索引CREATE INDEX idx_mv_union_auto_order_f_create_time ON mv_union_auto_order(f_create_time); 对下述查询语句进行explain分析。-- 改为从实例化视图查询withorder_dataas(selectf_city_nameasareaName,casewhenSUM(CASEWHENCURRENT_DATE-INTERVAL1 yearsf_create_timeTHEN1ELSE0END)isnullthen0elseSUM(CASEWHENCURRENT_DATE-INTERVAL1 yearsf_create_timeTHEN1ELSE0END)endastotalDispatch,casewhenSUM(CASEWHENf_status已回单andCURRENT_DATE-INTERVAL1 yearsf_create_timeTHEN1ELSE0END)isnullthen0elseSUM(CASEWHENf_status已回单andCURRENT_DATE-INTERVAL1 yearsf_create_timeTHEN1ELSE0END)endastotalReturn,casewhenSUM(CASEWHENCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATETHEN1ELSE0END)isnullthen0elseSUM(CASEWHENCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATETHEN1ELSE0END)endasfourWeekDispatch,casewhenSUM(CASEWHEN(f_accept_timeisnotnullandCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATE)THEN1ELSE0END)isnullthen0elseSUM(CASEWHEN(f_accept_timeisnotnullandCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATE)THEN1ELSE0END)endasfourWeekAccept,casewhenSUM(CASEWHEN(f_return_timeisnotnullandCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATE)THEN1ELSE0END)isnullthen0elseSUM(CASEWHEN(f_return_timeisnotnullandCURRENT_DATE-INTERVAL4 weeksf_create_timeANDf_create_timeCURRENT_DATE)THEN1ELSE0END)endasfourWeekReturn,casewhenSUM(CASEWHEN(f_status!挂起andf_if_closed是anddate_trunc(week,CURRENT_DATE-INTERVAL5 weeks)f_create_timeANDf_create_timedate_trunc(week,CURRENT_DATE-INTERVAL4 weeks))THEN1ELSE0END)isnullthen0elseSUM(CASEWHEN(f_status!挂起andf_if_closed是anddate_trunc(week,CURRENT_DATE-INTERVAL5 weeks)f_create_timeANDf_create_timedate_trunc(week,CURRENT_DATE-INTERVAL4 weeks))THEN1ELSE0END)endasfourWeekClose,casewhenSUM(CASEWHEN(f_status!挂起anddate_trunc(week,CURRENT_DATE-INTERVAL5 weeks)f_create_timeANDf_create_timedate_trunc(week,CURRENT_DATE-INTERVAL4 weeks))THEN1ELSE0END)isnullthen0elseSUM(CASEWHEN(f_status!挂起anddate_trunc(week,CURRENT_DATE-INTERVAL5 weeks)f_create_timeANDf_create_timedate_trunc(week,CURRENT_DATE-INTERVAL4 weeks))THEN1ELSE0END)endasfour_weeks_close_ratio_denominator,SUM(CASEWHENf_status!挂起andf_if_closed是andCURRENT_DATE-INTERVAL1 yearsf_create_timeTHEN1ELSE0END)ASallClose,SUM(CASEWHENf_accept_timeISNOTNULLANDf_accept_timef_create_timeINTERVAL2 hoursANDf_create_timeDATE_TRUNC(week,NOW())-INTERVAL4 weeksTHEN1ELSE0END)ASacceptInTime,SUM(CASEWHENf_return_timeISNOTNULLANDf_return_timef_create_timeINTERVAL24 hoursANDf_create_timeDATE_TRUNC(week,NOW())-INTERVAL4 weeksTHEN1ELSE0END)ASreturnInTimefrommv_union_auto_ordergroupbyf_city_name)SELECTareaNameASareaName,totalDispatchAStotalDispatch,totalReturnAStotalReturn,allCloseASallClose,fourWeekDispatchASfourWeekDispatch,fourWeekAcceptASfourWeekAccept,fourWeekReturnASfourWeekReturn,CASEWHENfourWeekDispatch0THEN0%ELSEROUND((fourWeekAccept::decimal/fourWeekDispatch)*100,2)||%ENDASfourWeekAcceptRate,CASEWHENfourWeekDispatch0THEN0%ELSEROUND((fourWeekReturn::decimal/fourWeekDispatch)*100,2)||%ENDASfourWeekReturnRate,fourWeekCloseASfourWeekClose,four_weeks_close_ratio_denominatorasfourWeeksCloseRatioDenominator,CASEWHENfour_weeks_close_ratio_denominator0THEN100%ELSEROUND((fourWeekClose::decimal/four_weeks_close_ratio_denominator)*100,2)||%ENDAScloseRate,CASEWHENtotalDispatch0THEN0%ELSEROUND((allClose::decimal/totalDispatch)*100,2)||%ENDASallCloseRate,acceptInTimeASacceptInTime,CASEWHENfourWeekAccept0THEN0%ELSEROUND((acceptInTime::decimal/fourWeekAccept)*100,2)||%ENDASacceptInTimeRate,returnInTimeASreturnInTime,CASEWHENfourWeekReturn0THEN0%ELSEROUND((returnInTime::decimal/fourWeekReturn)*100,2)||%ENDASreturnInTimeRateFROMorder_dataorderbyconvert_to(areaName,GBK)explain分析结果如下Sort(cost30089.59..30089.62rows13width311)SortKey:(convert_to((order_data.areaname)::text,GBK::name))-Subquery Scanonorder_data(cost30080.93..30089.35rows13width311)-Finalize GroupAggregate(cost30080.93..30087.43rows13width87)GroupKey: mv_union_auto_order.f_city_name-GatherMerge(cost30080.93..30085.55rows39width87)Workers Planned:3-Sort(cost29080.89..29080.92rows13width87)SortKey: mv_union_auto_order.f_city_name-PartialHashAggregate(cost29080.52..29080.65rows13width87)GroupKey: mv_union_auto_order.f_city_name-Parallel Seq Scanonmv_union_auto_order(cost0.00..20277.33rows51033width45)根据提供的执行计划该查询没有使用索引而是进行了并行顺序扫描Parallel Seq Scan关键证据执行计划中最底层的操作是- Parallel Seq Scan on mv_union_auto_order (cost0.00…20277.33 rows51033 width45)Seq Scan 表示顺序扫描即逐行读取整个表的数据。没有出现 Index Scan、Bitmap Index Scan 或 Index Only Scan 等与索引相关的操作。为什么没有使用索引1、查询性质该查询最终进行了分组聚合GroupAggregate / HashAggregate和排序Sort。优化器评估后认为即使存在索引使用顺序扫描配合哈希聚合的成本可能更低尤其是当需要读取表中大部分数据时本例中扫描了约 51,033 行。2、并行执行优化器选择了并行顺序扫描Parallel Seq Scan说明表可能较大但缺乏能有效过滤数据的索引条件。查询中没有 WHERE 子句或过滤条件极弱导致需要扫描全表。3、排序表达式最终的 Sort Key 使用了函数 convert_to((order_data.areaname)::text, ‘GBK’::name)。如果该列上没有对应的函数索引排序阶段也无法利用索引避免显式排序。结论执行计划明确显示为全表顺序扫描因此没有走索引。若需优化可考虑添加合适的索引如针对分组/排序列或函数索引。检查是否有必要的过滤条件减少扫描范围。