ChrisM@Work (5/9/2013) kapil_kk (5/9/2013)
What does its execution plan say? Are there any missing indexes? How big are the tables? Check the statistics on them.
You can replace the IN with EXISTS and NOT EXISTS.
INdexes are there on the columns.. for less data it works fine but when data gets increased it takes more time to execute
Post the ACTUAL execution plan as a .sqlplan attachment so folks can analyse it.
Here's a close equivalent written in a more conventional style for your perusal:
SELECT y.id, y.english_name
FROM ys_movements y
INNER JOIN (
SELECT DISTINCT p.object_id
FROM profiles_useritems p
INNER JOIN django_content_type d
ON d.id = p.content_type_id
INNER JOIN follow_follow f
ON f.target_user_id = d.user_id
AND f.user_id = 8
WHERE d.name = 'movements'
AND d.app_label = 'yoga')
AND NOT EXISTS (
FROM auth_user a
WHERE a.is_staff = 1
AND a.id = d.user_id)
ON f.object_id = y.id
WHERE y.is_private =0
Kapil, as IgorMi and Chris suggested, EXISTS and NOT EXISTS works way faster that IN and NOT IN. The above query is perfect example of how it should be used. Eventually it gets better as the number of rows grow. cheers. :-)