主页 / dba / 问题 / 210278
Accepted
cal
Asked: 2018-06-22 08:36:04 +0800 CST

在 Postgres 中使用 ORDER BY 时查询速度慢

  • 772

这个查询

SELECT *
FROM
  ( SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids 
NATURAL INNER JOIN events;

返回 456 个结果，耗时约 20 毫秒。

添加 ORDER BY

SELECT *
FROM
  (SELECT event_id
   FROM events_201806
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201805
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201804
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN'
   UNION ALL SELECT event_id
   FROM events_201803
   WHERE registry_name='$VCZN://externalcenter/ninaExtCenter_ZN') AS ids 
NATURAL INNER JOIN events
ORDER BY event_ts DESC;

导致查询需要很长时间才能完成，大约 10 分钟。

关于如何解决这个问题的任何指示？

postgresql performance

1 个回答

  1. Best Answer

    尝试将原始（快速）查询放入公用表表达式中：

    with my_events as (
  select *
  from (
    ...
  ) as ids
     join events on ids.event_id = events.id
)
select *
from my_events
order by event_ts desc;

    CTE 独立于查询的其余部分进行优化，因此应该使用快速计划运行基本查询。订购 456 行应该相当快。

    • 2

相关问题