相同查询在Oracle耗时0.04s在PostgreSQL需49.8分钟如何优化

可直接落地的优化方案 #

• 删除冗余结构：原查询的外层子查询包裹完全无意义，且子查询内的ORDER BY在没有外层排序需求、同时搭配DISTINCT的场景下属于无效操作，PostgreSQL不会自动省略该排序步骤，数据集较大时会消耗大量时间，直接删除即可。
• 移除不必要的DISTINCT：如果ID列是该表的主键/唯一键，那么查询结果天然不会有重复行，DISTINCT带来的全局去重排序开销完全是冗余的，直接删除即可。如果确实存在重复行的场景，可将DISTINCT替换为PostgreSQL特有的更高效的DISTINCT ON ("ID")，按ID去重仅保留第一条，性能远高于全列去重。
• 创建覆盖索引避免回表：针对查询的过滤条件和返回列创建联合覆盖索引，让查询直接走索引仅扫描，无需访问表数据，速度会有数量级提升，索引创建语句如下：

CREATE INDEX idx_all_service_details_query ON MZP_ADP.ALL_SERVICE_DETAILS 
("ZIP_CODE", "MAKE_LIVE", "LOCATION_ID", "IS_ACTIVE", "END_DATE" DESC, "CREATED_DT" DESC)
INCLUDE ("ID", "IMAGE", "TITLE", "SERVICE_DESC", "STATUS", "ACTION", "REMOVAL_TEXT", "SERVICE_PROVIDER", "SERVICE_PROVIDER_NAME");

• 检查类型匹配：确认表中END_DATE列的类型为TIMESTAMP WITHOUT TIME ZONE，避免隐式类型转换导致索引失效。

优化后的查询语句 #

SELECT -- 如果ID不是唯一键可保留DISTINCT/DISTINCT ON ("ID")
    "ID","IMAGE","TITLE","SERVICE_DESC",
    COALESCE("STATUS",'N') as "STATUS",
    "ACTION","REMOVAL_TEXT","SERVICE_PROVIDER",
    "SERVICE_PROVIDER_NAME" 
FROM MZP_ADP.ALL_SERVICE_DETAILS 
WHERE "ZIP_CODE"='55005' 
  AND "MAKE_LIVE" = 'Y'
  AND "LOCATION_ID" = '2407605' 
  AND "END_DATE" > CURRENT_TIMESTAMP(0)::TIMESTAMP WITHOUT TIME ZONE 
  AND "IS_ACTIVE" = 'Y' 
ORDER BY "CREATED_DT" DESC; -- 无排序需求可直接删除

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