Sample Header Ad - 728x90

SELECT operation with four joins results in bad performance

1 vote
1 answer
157 views
I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places. **Resources** - Query: SELECT DISTINCT doers.id FROM doers JOIN doer_locations dl ON dl.doer_id = doers.id JOIN job_places jp ON (jp.lat - 0.3147625620715557) dl.lat AND (jp.lng - 0.5001626620527362) dl.lng LEFT JOIN job_realizations jr ON jr.job_place_id = jp.id AND jr.status IN (1, 2, 3, 4) LEFT JOIN job_realizations jrpd ON jrpd.job_place_id = jp.id AND jrpd.doer_id = doers.id AND jrpd.status IN (1, 2, 3, 4) WHERE (jp.job_id = 1 AND doers.id IS NOT NULL) GROUP BY doers.id, jp.id HAVING COUNT(DISTINCT jr.id) < jp.realizations_per_place AND COUNT(DISTINCT jrpd.id) < jp.realizations_per_place_per_doer - [Depesz explain](https://explain.depesz.com/s/jMHP) - [Raw explain analyze](https://pastebin.com/aY4L33iY) - [Simplified Schema](https://www.db-fiddle.com/f/r2LFERkLEoo9fKYeTKQ8S6/1) **Consideration** I'm not sure if I read the explain correctly but it seems it loses on performance especially when it calculates stuff on the run also HAVING COUNT(DISTINCT) seems pretty expensive. **Additional information** The type of both the lat and long columns is float.
Asked by mist (29 rep)
Oct 1, 2018, 11:22 AM
Last activity: Jul 11, 2025, 05:06 AM