添加LIMIT 10，使PostgreSQL查询速度变慢的神奇操作！1.7秒变成28.2秒！

在昨天的博客文章“用全文本搜索解决方案替换列搜索”中，我想提到如何在快速运行的查询中添加LIMIT 10使其运行速度变慢。直到今天早上我才记得那个问题。幸运的是，解决方法是低成本的。

查询

这是我运行的查询：

EXPLAIN ANALYSE
WITH short_list AS (
    SELECT
        port_id
    FROM
        generate_plist
    WHERE
        textsearchable_index_col @@ to_tsquery('share/examples/acme.sh/dnsapi/dns_1984hosting.sh')
)
  select CL.commit_date - SystemTimeAdjust() AS last_commit_date, 
         P.id, 
         E.name as port,
         C.name as category, 
...
         P.uses  
       FROM short_list, ports P
       LEFT OUTER JOIN ports_vulnerable    PV  ON PV.port_id       = P.id
       LEFT OUTER JOIN commit_log          CL  ON P.last_commit_id = CL.id,
       element_pathname EP,
       categories C, element E 
 WHERE P.id = short_list.port_id
   AND P.element_id = EP.element_id and EP.pathname like '/ports/head/%'
   AND P.category_id  = C.id
   AND P.element_id   = E.id  AND E.status = 'A';

这个计划（在https://explain.depesz.com/s/Bkkg上找到）对我来说足够快。

加LIMIT 10

在FreshPorts搜索页面（不是在生产环境中，而是在开发人员网站上）中使用了此功能。它需要一个限制，所以我添加了LIMIT 10。

该计划改为对大表进行顺序扫描。动臂，28秒。那是不令人满意的。该计划位于https://explain.depesz.com/s/oJZB。

修复

正如托马斯·赫斯特（Thomas Hurst）在对我的推文的回复中所指出的那样，解决方法围绕CTE（公用表表达式）展开。最初，这使我感到困惑，因为查询已在使用CTE。这就是查询的WITH short_list AS部分。

然后，我阅读了他回复的“按材料分类”部分，并开始对此进行阅读。我将查询更改为：

EXPLAIN ANALYSE
WITH short_list AS MATERIALIZED (
    SELECT
        port_id
    FROM
        generate_plist
    WHERE
        textsearchable_index_col @@ to_tsquery('share/examples/acme.sh/dnsapi/dns_1984hosting.sh')
)
  select CL.commit_date - SystemTimeAdjust() AS last_commit_date, 
         P.id, 
         E.name as port,
         C.name as category, 
...
         P.uses  
       FROM short_list, ports P
       LEFT OUTER JOIN ports_vulnerable    PV  ON PV.port_id       = P.id
       LEFT OUTER JOIN commit_log          CL  ON P.last_commit_id = CL.id,
       element_pathname EP,
       categories C, element E 
 WHERE P.id = short_list.port_id
   AND P.element_id = EP.element_id and EP.pathname like '/ports/head/%'
   AND P.category_id  = C.id
   AND P.element_id   = E.id  AND E.status = 'A'
LIMIT 10;

您可以在第2行上看到更改：AS MATERIALIZED。

该计划（可在https://explain.depesz.com/s/UBps上找到）耗时1.4秒，并返回使用索引扫描。

作者：Dan Langille
文章来源：https://news.freshports.org/2021/03/09/a-postgresql-query-runs-in-1-7s-add-a-limit-10-runs-in-28-2-seconds/