From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Kai Sellgren <kaisellgren(at)gmail(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Issue with query scanning through all data even with indexes |
Date: | 2014-01-16 15:41:49 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC5B42D5F2@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Kai Sellgren
Sent: Thursday, January 09, 2014 4:37 PM
To: pgsql-performance(at)postgresql(dot)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:
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
From | Date | Subject | |
---|---|---|---|
Next Message | Piotr Gasidło | 2014-01-17 22:57:54 | Wrong index selection |
Previous Message | Guillaume Cottenceau | 2014-01-16 08:42:57 | Re: Slow counting on v9.3 |