更快地替代從 2 個查詢中計算值並使用 WITH 進行選擇

我有一個查詢，我必須讓它執行得更快。這是我最好的鏡頭。有什麼建議可以改進嗎？

WITH cm 
    AS (SELECT Count(*) AS count_messages 
        FROM   messages AS m 
        WHERE  EXISTS (SELECT 1 
                       FROM   order_items AS o 
                              inner join orders ox 
                                      ON ox.order_id = o.order_id 
                       WHERE  m.product_id = o.product_id 
                              AND m.message_type = 0 
                              AND m.seller_id IN( 3, 2, 6, 1, 9 ) 
                              AND current_date >= ox.order_creation_date)), 
    co 
    AS (SELECT Count(*) AS count_order_items 
        FROM   order_items AS o 
        WHERE  EXISTS (SELECT 1 
                       FROM   messages AS m 
                              inner join orders oy 
                                      ON oy.order_id = o.order_id 
                       WHERE  m.product_id = o.product_id 
                              AND m.message_type = 0 
                              AND m.seller_id IN( 3, 2, 6, 1, 9 ) 
                              AND current_date >= oy.order_creation_date)) 
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL * 100 AS 
      result 
FROM   cm, 
      co 

在不知道任何鍵等的情況下進行查詢重寫有點棘手，但如果我沒記錯的話 cm 可以重寫為：

SELECT Count(distinct m.message_id) AS count_messages
FROM   messages AS m
JOIN   order_items AS o
   ON m.product_id = o.product_id
JOIN   orders ox
   ON ox.order_id = o.order_id                               
WHERE m.message_type = 0
 AND m.seller_id IN( 3, 2, 6, 1, 9 )                               
 AND current_date >= ox.order_creation_date

A |x| B <=> B |x| A使用相同的技術和給我們的事實：

SELECT Count(distinct o.order_item_id) AS count_order_items
FROM   messages AS m
JOIN   order_items AS o
   ON m.product_id = o.product_id
JOIN   orders ox
   ON ox.order_id = o.order_id
WHERE m.message_type = 0
 AND m.seller_id IN( 3, 2, 6, 1, 9 )
 AND current_date >= ox.order_creation_date

為公司 這可以組合成：

SELECT 100*Count(distinct m.message_id) :: Decimal 
        / Count(distinct o.order_item_id) :: Decimal 
FROM   messages AS m
JOIN   order_items AS o
   ON m.product_id = o.product_id
JOIN   orders ox
   ON ox.order_id = o.order_id
WHERE m.message_type = 0
 AND m.seller_id IN( 3, 2, 6, 1, 9 )
 AND current_date >= ox.order_creation_date

引用自：https://dba.stackexchange.com/questions/208102