CODEWITHSUNDEEP
sqlpostgresqlquery-performance
enneenne
enneenne

Reputation: 219

How to make query faster

Here is my code:

SELECT m.id, (SELECT count(r.id) 
              FROM reports as r 
              WHERE r.date BETWEEN NOW()-INTERVAL '7 days' AND NOW() 
                and r.decision = 'ACCEPTED' 
                and r.customer_id in (SELECT c.id 
                                      FROM customers as c 
                                      WHERE c.manager_id = m.id)) as count 
FROM managers as m;

Where tables are managers, customers and reports. I want to SELECT, for every manager, number of reports all of his customers have made (with selected properties). Problem with this code is that it takes too much time and somehow cannot find a better way of building this query. Do you have any idea?

Upvotes: 0

Views: 55

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

I think , a standard inner join among those three table with indexes defined on reports.date, reports.customer_id and customers.manager_id columns as mentioned on the comment( moreover considering managers.id and customer.id columns as expected to be primary keys which already have unique indexes on them ) will suffice for performance point of view :

SELECT m.id, count(r.id)
  FROM customers c
  JOIN managers m ON r.customer_id = c.id
  JOIN reports r ON  m.id = c.manager_id
 WHERE r.date BETWEEN NOW() - INTERVAL '7 days' AND NOW()
   AND r.decision = 'ACCEPTED'
 GROUP BY m.id

Upvotes: 1

Related Questions