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?

  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
  • What's the schema on both the tables? Mar 30, 2018 at 7:50
  • 3
    Please Edit your question and add the create table statements for the tables in question (including all indexes) and the execution plan generated using explain (analyze, buffers). Formatted text please, no screen shots Mar 30, 2018 at 7:52
  • Explain analyse added. For other info you asked is there a query tat I can quickly generate what you need to analyse?
    – SNaRe
    Mar 30, 2018 at 8:05
  • Yes, with psql, paste the result \d messages and \d order_items Mar 30, 2018 at 8:05

2 Answers 2


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:

  cm AS
    ( SELECT COUNT(*) AS count_messages
      FROM messages AS m
            ( 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 
            ( 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 ;

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


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..

  • message_id and order_item_id are primary keys. Also these 2 give me different results. Also since this is a joined table message_id and order_item_id may repeat themselves in different column values. So that I need to get distinct.
    – SNaRe
    Mar 30, 2018 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.