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
create table
statements for the tables in question (including all indexes) and the execution plan generated usingexplain (analyze, buffers)
. Formatted text please, no screen shots\d messages
and\d order_items