Re: Issue with query scanning through all data even with indexes

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