From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Kai Sellgren
Sent: Thursday, January 09, 2014 4:37 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Issue with query scanning through all data even with indexes
Hi,
I have a query that has each field used in conditions + sort indexed, but it scans through all data.
The query in question looks like:
http://pastie.org/8618562
I have each of those condition fields indexed:
NewsArticle.groupId
NewsArticle.sharedToCommunityIds
NewsArticle.sourceFilterIds
CommunityGroupLink.communityId
CommunityGroupLink.groupId
SourceFilter.groupId
SourceFilter.communityId
This is the data output for explain http://d.pr/i/VGT3
And in visual http://d.pr/i/mqiN
Line 7 says rows=99173 which makes it real slow (it can take up to a minute to run).
Do you have any ideas? All of them are appreciated!
Cheers,
--
Yours sincerely,
Kai Sellgren
Could you try to move WHERE clause conditions into JOIN conditions, something like this:
SELECT "NewsArticle"."id"
FROM "NewsArticle"
LEFT JOIN "CommunityGroupLink" ON "CommunityGroupLink"."communityId" = 1538 AND ("CommunityGroupLink"."groupId" =
"NewsArticle"."groupId")
AND((1538 = ANY ("NewsArticle"."sharedToCommunityIds") OR ("CommunityGroupLink"."id" IS NOT NULL)))
LEFT JOIN "SourceFilter" ON "SourceFilter"."communityId" = 1538 AND "SourceFilter"."groupId" = "NewsArticle"."groupId"
AND(("SourceFilter"."id" IS NULL OR "SourceFilter"."id" = ANY("NewsArticle"."sourceFilterIds")));
Not sure what you do with "LIMIT 35" - it's not shown in "explain" plan.
Regards,
Igor Neyman