postgresql 优化Postgres查询从SELECT到JOIN(？)

ut6juiuv 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(146)

我现在有一个类似下面的psql查询。它需要很长时间才能执行，大约1：30。我想优化这个查询，但不必对同一个查询执行多个SELECT语句。

SELECT 

(SELECT COUNT(*) FROM message WHERE message.created_by_client_id = v_client.id) AS "inboundMessages",

(SELECT v_form."isReceived" FROM v_form 
WHERE (v_form."client" ->> 'id')::integer = v_client."id" 
AND v_form."formTypeId" = 1
LIMIT 1 ) AS "isIntakeReceived",

(SELECT v_form."isReceived" FROM v_form 
WHERE (v_form."client" ->> 'id')::integer = v_client."id" 
AND v_form."formTypeId" = 2
LIMIT 1 ) AS "isFollowUpReceived",
* 
FROM v_client

字符串
我尝试将SELECTS重写为joint，但它们返回多行。我只需要最新的形式和由客户端创建的所有消息的计数。

postgresql

来源：https://stackoverflow.com/questions/76830760/optimize-postgres-query-from-select-to-join

1条答案

按热度按时间

b4wnujal1#

试试这个，也许会有帮助：

SELECT q.*,
       count(*) AS "inboundMessages"
FROM (SELECT DISTINCT ON (v_client.id)
             v_client.*,
             f1."isReceived" AS "isIntakeReceived",
             f2."isReceived" AS "isFollowUpReceived"
      FROM v_client
         JOIN v_form AS f1
            ON (f1."client" ->> 'id')::integer = v_client."id" 
               AND f1."formTypeId" = 1
         JOIN v_form AS f2
            ON (f2."client" ->> 'id')::integer = v_client."id" 
               AND f2."formTypeId" = 2) AS q
   JOIN message
      ON message.created_by_client_id = q.id
GROUP BY /* all columns from "q" */;

字符串
可以帮助您的原始查询的索引：

关于v_form (((client ->> 'id')::integer), "formTypeId")
关于message (created_by_client_id)

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 优化Postgres查询从SELECT到JOIN(？)

1条答案

相关问题

热门标签

最新问答