Optimizing the query with 2 distinct counts in select

Question

This SQL works perfectly but it is pretty slow for me. I did all performance improvement in my database. More complex queries works faster. ( Postgresql 10.x )

How can I optimize or make this query faster?

SELECT
  COUNT(DISTINCT a.message_id) :: DECIMAL / COUNT(DISTINCT b.order_item_id) :: DECIMAL
FROM messages a
INNER JOIN order_items b
  ON a.product_id = b.product_id;


Aggregate  (cost=53169.94..53169.96 rows=1 width=32) (actual time=10281.758..10281.758 rows=1 loops=1)
  ->  Nested Loop  (cost=0.29..14266.71 rows=7780646 width=8) (actual time=0.015..4054.449 rows=7811063 loops=1)
        ->  Seq Scan on messages a  (cost=0.00..775.82 rows=15582 width=8) (actual time=0.007..4.527 rows=15582 loops=1)
        ->  Index Scan using order_items_product_id on order_items b  (cost=0.29..0.73 rows=14 width=8) (actual time=0.005..0.174 rows=501 loops=15582)
              Index Cond: (product_id = a.product_id)
Planning time: 0.450 ms
Execution time: 10281.813 ms

Answer 1

There is a many-to-many relationship between messages and order_items through products, as a product can appear in many order_items and many messages and also a message (or an order_item) can have many products.

This results in the query producing a variation of a CROSS JOIN, and thus the need for using DISTINCT in the counts and consequently the poor performance.

I'd suggest you try adding composite indexes on both tables, on (product_id, order_item_id), (product_id, message_id) and see if they are used.

Another approach would be to rewrite the query so it doesn't use DISTINCT:

WITH 
  cm AS
    ( SELECT COUNT(*) AS count_messages
      FROM messages AS m
      WHERE EXISTS
            ( SELECT 1 
              FROM order_items AS o
              WHERE m.product_id = o.product_id
            )
    ),
  co AS
    ( SELECT COUNT(*) AS count_order_items
      FROM order_items AS o 
      WHERE EXISTS
            ( SELECT 1 
              FROM messages AS m
              WHERE m.product_id = o.product_id
            )
    )
SELECT cm.count_messages :: DECIMAL / co.count_order_items :: DECIMAL
         AS result
FROM cm, co ;

Answer 2

It seems unlikely you need the first DISTINCT unless message_id is not `UNIQUE?

That is to say, these two are the same,

COUNT(DISTINCT a.message_id) :: DECIMAL / COUNT(DISTINCT b.order_item_id) :: DECIMAL

vs..

COUNT(a.message_id) :: DECIMAL / COUNT(DISTINCT b.order_item_id) :: DECIMAL

The other DISTINCT is a lot more difficult and would require a loose index scan to speed up..