如何优化涉及大表关联的复杂PostgreSQL查询？

问题背景 #

我需要优化AI聊天机器人系统中的复杂PostgreSQL查询，该查询返回符合筛选条件的唯一对话统计数据。目前已定位问题：关联10540420行的fact_milestone_event表的特定inner join子句，导致查询耗时从约2秒骤增至约9分钟。

原查询语句（注释问题join） #

select 
  "conversation_table"."conversationid" as "conversationId", 
  "du"."userid" as "userId", 
  "du"."username" as "userName", 
  "du"."channel" as "channel", 
  COALESCE(user_events.count, 0):: integer as "eventCount", 
  BOOL_OR(dc.operator_messages > 0) as "operator", 
  DATE_PART(
    'epoch', dc.first_message_timestamp
  )* 1000 as "firstMessageDate", 
  DATE_PART(
    'epoch', dc.last_message_timestamp
  )* 1000 as "lastMessageDate", 
  COUNT(*) OVER():: integer as "totalUniqueUsers" 
from 
  "fact_conversation_data" as "conversation_table" 
  inner join "dim_user" as "du" on "conversation_table"."dim_user_user_id" = "du"."user_id" 
  inner join "dim_time" as "dt" on "conversation_table"."dim_time_time_id" = "dt"."time_id" 
  inner join "dim_conversation" as "dc" on "conversation_table"."conversationid" = "dc"."conversationid" 
--   inner join (
--     select 
--       "conversationid" 
--     from 
--       "fact_milestone_event" as fme
--     where 
--       fme.dim_segment_segment_id in ('20736b82-4515-411f-9bc8-cf4d84ad69ac')
--       group by "conversationid"
--   ) as "fme" on "fme"."conversationid" = "conversation_table"."conversationid" 
  left join (
    select 
      "fme"."conversationid", 
      count("fme"."event_id") 
    from 
      "fact_milestone_event" as "fme" 
    where 
      "fme"."timestamp" >= '2022-12-18 11:00:00.000' 
      and "fme"."timestamp" <= '2023-01-18 10:59:59.999' 
      and "fme"."dim_tenant_tenant_id" = '4621ed8f-d8a4-46e2-a8de-5710751b16b9' 
    group by 
      "fme"."conversationid"
  ) as "user_events" on "conversation_table"."conversationid" = "user_events"."conversationid" 
where 
  "conversation_table"."timestamp" >= '2022-12-18 11:00:00.000' 
  and "conversation_table"."timestamp" <= '2023-01-18 10:59:59.999' 
  and "dt"."bot_zone" = 'Pacific/Auckland' 
  and "du"."is_platform_user" <> true 
  and "conversation_table"."conversationid" in (
    select 
      "notEmptyConservation_table"."conversationid" 
    from 
      (
        select 
          sum("user_messages") as "total_user_message_count", 
          "conversationid" 
        from 
          "fact_conversation_data" 
        where 
          "fact_conversation_data"."dim_tenant_tenant_id" = '4621ed8f-d8a4-46e2-a8de-5710751b16b9' 
          and "fact_conversation_data"."timestamp" >= '2022-12-18 11:00:00.000' 
          and "fact_conversation_data"."timestamp" <= '2023-01-18 10:59:59.999' 
        group by 
          "conversationid"
      ) as "notEmptyConservation_table" 
    where 
      "notEmptyConservation_table"."total_user_message_count" > 1
  ) 
  and "conversation_table"."dim_tenant_tenant_id" = '4621ed8f-d8a4-46e2-a8de-5710751b16b9' 
  and (
    "conversation_table"."user_messages" > 0
  ) 
group by 
  "user_events"."count", 
  "conversation_table"."conversationid", 
  "du"."userid", 
  "du"."username", 
  "du"."channel", 
  "dc"."first_message_timestamp", 
  "dc"."last_message_timestamp" 
order by 
  "lastMessageDate" asc 
limit 
  20

已尝试的优化 #

1. 将问题inner join替换为WHERE子句嵌套查询
2. 为inner join子查询添加更多过滤条件，改写后的子查询如下：

inner join (
  select "conversationid" 
  from "fact_milestone_event" as fme
  where 
  fme.dim_segment_segment_id in ('20736b82-4515-411f-9bc8-cf4d84ad69ac') AND
  fme.timestamp >= '2022-12-18 11:00:00.000' AND
  fme.timestamp <= '2023-01-18 10:59:59.999' AND
  fme.dim_tenant_tenant_id = '4621ed8f-d8a4-46e2-a8de-5710751b16b9'
  group by "conversationid"
) as "fme" on "fme"."conversationid" = "conversation_table"."conversationid" 

两张主表的索引信息 #

优化建议 #

• 创建复合索引加速子查询：当前fact_milestone_event的索引均为单字段，针对问题子查询的过滤条件，创建包含dim_segment_segment_id、dim_tenant_tenant_id、timestamp和conversationid的复合索引，让数据库直接通过索引获取所需数据，避免大量数据扫描：

CREATE INDEX idx_fme_segment_tenant_time_conv ON fact_milestone_event 
USING btree (dim_segment_segment_id, dim_tenant_tenant_id, timestamp, conversationid);

• 替换INNER JOIN为EXISTS子句：判断对话是否存在符合条件的里程碑事件时，EXISTS比JOIN更高效，不会生成中间关联表，减少数据处理量。在原查询的WHERE条件中添加：

AND EXISTS (
    SELECT 1 FROM fact_milestone_event fme
    WHERE fme.conversationid = conversation_table.conversationid
      AND fme.dim_segment_segment_id = '20736b82-4515-411f-9bc8-cf4d84ad69ac'
      AND fme.timestamp >= '2022-12-18 11:00:00.000'
      AND fme.timestamp <= '2023-01-18 10:59:59.999'
      AND fme.dim_tenant_tenant_id = '4621ed8f-d8a4-46e2-a8de-5710751b16b9'
)

• 简化嵌套子查询：原查询中判断对话消息数>1的两层嵌套子查询可简化为单层，用HAVING子句直接过滤，降低查询复杂度：

AND conversation_table.conversationid IN (
    SELECT conversationid
    FROM fact_conversation_data
    WHERE dim_tenant_tenant_id = '4621ed8f-d8a4-46e2-a8de-5710751b16b9'
      AND timestamp >= '2022-12-18 11:00:00.000'
      AND timestamp <= '2023-01-18 10:59:59.999'
    GROUP BY conversationid
    HAVING SUM(user_messages) > 1
)

• 更新统计信息：执行以下命令更新表统计信息，让PostgreSQL优化器能准确估算数据分布，选择最优执行计划：

ANALYZE fact_milestone_event;
ANALYZE fact_conversation_data;

• 检查JOIN顺序：原查询先关联维度表再过滤数据，可尝试先对fact_conversation_data应用WHERE条件过滤，再关联维度表（PostgreSQL优化器通常会自动调整，但手动指定若更优也可尝试）。

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