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
Last activity: Jul 11, 2025, 05:06 AM