From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
