1

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
  • What's the schema on both the tables? – Evan Carroll Mar 30 '18 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 – a_horse_with_no_name Mar 30 '18 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 '18 at 8:05
  • Yes, with psql, paste the result \d messages and \d order_items – Evan Carroll Mar 30 '18 at 8:05
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:

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 ;
0

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

  • 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 '18 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.